如何按组查找列的总和 [英] How to find the sum of a column by groups

问题描述

大家好，

我有一张桌子，上面列出了我们使用的各种材料以及质量数.
我想将不同的原始物料编号"的质量加在一起，并按编号进行分组.轮次可能在同一天的不同时间发生，因此，在这种情况下，我想按最大时间对它们进行分组.

Hi Guys,

I have a table with the various materials that we use and there mass numbers.
I would like to add together the masses of the different "raw material numbers" and group them by round no. The round no''s could occur at vaious different times on the same day, therefore In this case I would want to group them by the maximum time.

STOCK_DATE           ROUND_NO   RAW_MAT_NO_1   RAW_MAT_MASS_1
-------------------- --------     ------       ----------------------
2012-02-22 10:09:00      9         3          11980
2012-02-22 10:14:00      9         4          1641
2012-02-22 10:19:00      9         3          12059
2012-02-22 10:24:00      9         4          1624
2012-02-22 10:30:00      9         3          11980
2012-02-22 10:37:00      9         4          1624

我尝试使用下面的代码:

I have tried using the code below :

select
                 max(stock_date) as latest,round_no,
             sum(nvl(decode(raw_mat_no_1,3,raw_mat_mass_1,0),0) +
             nvl(decode(raw_mat_no_2,3,raw_mat_mass_2,0),0) +
             nvl(decode(raw_mat_no_3,3,raw_mat_mass_3,0),0) +
             nvl(decode(raw_mat_no_4,3,raw_mat_mass_4,0),0) +
             nvl(decode(raw_mat_no_5,3,raw_mat_mass_5,0),0) +
             nvl(decode(raw_mat_no_6,3,raw_mat_mass_6,0),0)) as Coke
                from chargelog_practice
             where   stock_date >= to_date(' 2012-01-22 10:09:00 ','yyyy-mm-dd hh24:mi:ss')
                     and   stock_date < to_date(' 2012-02-22 10:09:00 ','yyyy-mm-dd hh24:mi:ss')
           group by round_no
           order by latest desc

并检索以下结果:

and retrieve the results as below:

LATEST                    ROUND_NO               RAW_MAT_NO_3
------------------------- ---------------------- ----------------------
2012-02-22 10:06:00       8                      636955
2012-02-22 09:40:00       7                      631990
2012-02-22 09:14:00       6                      632102
2012-02-22 08:48:00       5                      632542
2012-02-22 08:25:00       4                      631963
2012-02-22 08:04:00       3                      641828

这是正确的，但由于某些原因，RAW_MAT_NO_3质量不正确:

理想情况下，表格应如下所示:

This would be correct but for some reason the RAW_MAT_NO_3 masses are not correct:

Ideally the table would look like this :

DAY_OF_MNTH               ROUND_NO               COKE
------------------------- ---------------------- ----------------------

2012-02-22 09:14:00       6                      36000
2012-02-22 09:40:00       7                      36000
2012-02-22 10:06:00       8                      36001

我的代码有什么问题?

digimanus:将Oracle添加到标签中

What is wrong with my Code?

digimanus: added Oracle to the tags

推荐答案

尝试以下操作:

Try this:

select..............
where   stock_date = (select max(stock_date) from chargelog_practice C2 on C1.LATEST = C2.LATEST AND C1.ROUND_NO = C2.ROUND_NO AND C1.RAW_MAT_NO_3 = C2.RAW_MAT_NO_3)
group by round_no
order by latest desc

这篇关于如何按组查找列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！