如何按子类别和diapazone计算

Question

我有桌子

+----------+-------------+-----------+-------+
| Category | Subcategory | Diapazone | Summa |
+----------+-------------+-----------+-------+
| A        | M           |         1 |    10 |
| A        | M           |         2 |    20 |
| A        | S           |         3 |    30 |
| A        | S           |         4 |    40 |
| B        | M           |         5 |    50 |
| B        | S           |         6 |    60 |
| B        | S           |         7 |    70 |
| B        | S           |         8 |    80 |
+----------+-------------+-----------+-------+

我需要

+----------+-------------+-----+-----+-----+
| Category | Subcategory | 1-3 | 4-6 | 7-8 |
+----------+-------------+-----+-----+-----+
| A        | M           |  30 |   0 |   0 |
| A        | S           |  30 |  40 |   0 |
| B        | M           |   0 |  50 |   0 |
| B        | S           |   0 |  60 | 150 |
|          | total M     |  30 |  50 |   0 |
|          | total S     |  30 | 100 | 150 |
+----------+-------------+-----+-----+-----+

在重水区中，有一个类别和子类别的总和。你能帮我做这个吗？

Answer 1

您可以尝试使用条件聚合

select category, subcategory, 
       sum(Summa) FILTER (WHERE Diapazone between 1 and 3) as '1-3',
       sum(Summa) FILTER (WHERE Diapazone between 4 and 6) as '4-6',
       sum(Summa) FILTER (WHERE Diapazone between 7 and 8) as '7-8'
from tablename
group by category, subcategory
union all
select '', concat('Total ',subcategory), 
           sum(Summa) FILTER (WHERE Diapazone between 1 and 3) as '1-3',
           sum(Summa) FILTER (WHERE Diapazone between 4 and 6) as '4-6',
           sum(Summa) FILTER (WHERE Diapazone between 7 and 8) as '7-8'
from tablename
group by concat('Total ',subcategory)

Answer 2

select COALESCE(category, CONCAT('Total ',category)), 
       COALESCE(subcategory, CONCAT('Total ',subcategory)),
       sum(Summa) FILTER (WHERE Diapazone between 1 and 3) as '1-3',
       sum(Summa) FILTER (WHERE Diapazone between 4 and 6) as '4-6',
       sum(Summa) FILTER (WHERE Diapazone between 7 and 8) as '7-8'
from tablename
group by ROLLUP(category, subcategory)