sql - 使用嵌套聚合函数按行分组的总和

Question

我有两个表

产品:

Res    ID    Code    Type
1      A      A-01    High
2      A      A-02    Medium
3      B      B-02    Medium
4      B      B-03    High
5      C      C-01    Low
6      C      C-03    Low

预订:

Res    ID    High    Medium    Low
1      A     3       0         0
2      A     0       5         0
3      B     0       4         0
4      B     3       0         0
5      C     0       0         4
6      C     0       0         3

我正在尝试获取按 ID 分组的所有值的总和

我可以使用按 ID 分组的类型获取总和

select 
product.res,
product.type,
count(product.type),
case
    when product.type='high' then reservation.high
    when product.type='medium' then reservation.medium
    when product.type='low' then reservation.low
    else 0
end,

case
    when product.type='high' then reservation.high* count(product.type)
    when product.type='medium' then reservation.medium* count(product.type)
    when product.type='low' then reservation.low* count(product.type)
    else 0
end

from pallets

left join reservation
on product.res=reservation.res

group by 
product.id,
product.type,
product.res,
reservation.high,
reservation.medium,
reservation.low

by product.id, product.res

我知道需要获取按 ID 分组的所有值的总和。所以我要找的是

ID    Total
A     8
B     7
C     7

在尝试解决这个问题时，不断遇到嵌套聚合错误。我在 Postrgres 9.5 上运行

Answer 1

我希望事情就这么简单。让我们以预订表为例:

create table reservation (res int, id char(1), high int, medium int, low int);
insert into reservation values
(1,'A',3,0,0),
(2,'A',0,5,0),
(3,'B',0,4,0),
(4,'B',3,0,0),
(5,'C',0,0,4),
(6,'C',0,0,3);

select id, sum(high + medium + low)
from reservation
group by id
order by id;

结果:

| id | sum |
|----|-----|
|  A |   8 |
|  B |   7 |
|  C |   7 |

示例:http://sqlfiddle.com/#!15/cb874/2

编辑:

假设以下数据结构:

预订:

+------+------+------+--------+------+
| res  | id   | high | medium | low  |
+------+------+------+--------+------+
|    1 | A    |    3 |      0 |    0 |
|    2 | A    |    0 |      5 |    0 |
|    3 | B    |    0 |      4 |    0 |
|    4 | B    |    3 |      0 |    0 |
|    5 | C    |    0 |      0 |    4 |
|    6 | C    |    0 |      0 |    3 |
+------+------+------+--------+------+

产品:

+------+------+------+--------+
| res  | id   | code | type   |
+------+------+------+--------+
|    1 | A    | A-01 | High   |
|    1 | A    | A-01 | High   |
|    2 | A    | A-02 | Medium |
|    2 | A    | A-03 | Medium |
|    3 | B    | B-02 | Medium |
|    4 | B    | B-03 | High   |
|    5 | C    | C-01 | Low    |
|    6 | C    | C-03 | Low    |
+------+------+------+--------+

SQL:

select
  p.id,
  sum(case 
      when p.type = 'High' then r.high
      when p.type = 'Medium' then r.medium
      when p.type = 'Low' then r.low
      end) tot
from product p
inner join reservation r on p.res = r.res
group by p.id

结果:

+------+------+
| id   | tot  |
+------+------+
| A    |   16 |
| B    |    7 |
| C    |    7 |
+------+------+