将包含不同值的列聚合为一个

时间：2021-03-21 22:43:41

I have a table like so:

我有一张这样的桌子:

    type          load             total
    -------------------------------------
Default           base             26.2
Default           peak             29.625
NonDefault        base             26
NonDefault        peak             29.4

I'm currently attempting to sum the total column Sum(total) and group by the load, which leaves the type column needing to be aggregated.

我目前正在尝试将总列数Sum(total)和group按负载相加,这样就需要聚合类型列。

Most of the time type is the same (all Default) so I can simply add this to the grouping, however sometimes it isn't (as in the example). I want to be able to aggregate this column so that if both types are showing (Default and NonDefault) then I want the aggregate to output Default for all columns, which will allow me to carry on with the grouping.

大多数时间类型是相同的(所有默认值)所以我可以简单地将其添加到分组中,但有时它不是(如示例中所示)。我希望能够聚合此列,以便如果两种类型都显示(Default和NonDefault),那么我希望聚合输出所有列的Default,这将允许我继续进行分组。

I realise this may be a job for a custom function? Or are there any better ideas of how to condense this column?

我意识到这可能是一个自定义功能的工作?或者有没有更好的想法如何浓缩这个专栏?

2 个解决方案

#1

2  

select min(type) as type,
       load,
       sum(total) as total
from T
group by load

SQL Fiddle

#2

1  

select 
       sum(total), 
       min(type) 
from table 
group by load

#1

2  

select min(type) as type,
       load,
       sum(total) as total
from T
group by load

SQL Fiddle

#2

1  

select 
       sum(total), 
       min(type) 
from table 
group by load
标签：

相关文章