Combine multiple "group by" into single query?

Question

I'm trying to sum / calculate values according to different "group by", but am unable to figure out how to do it in a single query.

CREATE TABLE test(
col1    text,
col2    text,
col3    int
);

INSERT INTO test VALUES('A',  '',  10);
INSERT INTO test VALUES('A', 'A',  15);
INSERT INTO test VALUES( '', 'A', 100);
INSERT INTO test VALUES('B', 'C',   1);
INSERT INTO test VALUES('C',  '',  33);

I've figured out how to partially get what I'm looking for:

--(this might not be the "correct" way, just my experiments)
SELECT col1 AS name, sum(col3) as col1_sum FROM test GROUP BY col1;
SELECT col2 AS name, sum(col3) as col2_sum FROM test GROUP BY col2;

In addition to the above, I would like a calculation of the difference b_sum - a_sum, so the complete query result would be:

name     col1_sum     col2_sum     difference
----     --------     --------     ----------
A          25             115          90
B           1         (empty)          -1
C          33               1         -32
(empty)   100              43         -57

Any ideas how to get the output described above..? Thanks!

Gordon Linoff · Accepted Answer · 2017-05-10 19:38:07Z

2

I am thinking full outer join after aggregation:

select coalesce(t1.name, t2.name) as name, t1.col1_num, t2.col2_sum,
       coalesce(t2.col2_sum, 0) - coalesce(t1.col1_sum, 0) as diff
from (select col1 as name, sum(col3) as col1_sum
      from ttest
      group by col1
     ) t1 full outer join
     (select col2 as name, sum(col3) as col2_sum
      from ttest
      group by col2
     ) t2
     on t1.name = t2.name;

answered May 10, 2017 at 19:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vamsi Prabhala · Accepted Answer · 2017-05-10 19:34:39Z

1

Combine the results using UNION ALL and then subtract those values.

select name,max(col1_sum),max(col2_sum),coalesce(max(col2_sum),0)-coalesce(max(col1_sum),0)
from (
SELECT col1 AS name, sum(col3) as col1_sum,null col2_sum
FROM test 
GROUP BY col1
UNION ALL
SELECT col2, null, sum(col3) 
FROM test
GROUP BY col2
) t
GROUP BY name

answered May 10, 2017 at 19:34

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Comments

Clodoaldo Neto · Accepted Answer · 2017-05-10 19:59:38Z

1

with t as (
    select col1, col2, sum(col3), grouping((col1),(col2))
    from test
    group by grouping sets ((col1),(col2))
)
select
    coalesce(t1.col1, t1.col2) as col,
    t1.sum as col1_sum,
    t2.sum as col2_sum,
    coalesce(t2.sum, 0) - coalesce(t1.sum, 0) as difference
from t t1 full join t t2 on t1.col1 = t2.col2
where t1.col1 <> '' or t2.col2 <> '' or (t1.col1 = '' and t2.col2 = '')
;
 col | col1_sum | col2_sum | difference 
-----+----------+----------+------------
     |      100 |       43 |        -57
 A   |       25 |      115 |         90
 B   |        1 |          |         -1
 C   |       33 |        1 |        -32

answered May 10, 2017 at 19:59

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Comments

SqlZim · Accepted Answer · 2017-05-10 19:36:45Z

using common table expressions:

;with a as (
SELECT 
    col1 AS name
  , sum(col3) as col1_sum 
FROM test 
GROUP BY col1
)
, b as (
SELECT 
    col2 AS name
  , sum(col3) as col2_sum 
FROM test 
GROUP BY col2
)
select 
    a.name
  , a.col1_sum
  , b.col2_sum
  , coalesce(b.col2_sum,0) - coalesce(a.col1_sum,0) as difference
from a
  left join b
    on a.name = b.name
order by a.name

rextester demo: http://rextester.com/YROWT76204

returns:

+------+----------+----------+------------+
| name | col1_sum | col2_sum | difference |
+------+----------+----------+------------+
|      |      100 | 43       |        -57 |
| A    |       25 | 115      |         90 |
| B    |        1 | NULL     |         -1 |
| C    |       33 | 1        |        -32 |
+------+----------+----------+------------+

If the first query doesn't have all the names, then switch from a left join to a full join and use coalesce(a.name,b.name) as name.

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

select a.name, col1_sum, col2_sum , coalesce(col2_sum,0) - coalesce(col1_sum,0) as diff
from
     (SELECT case when col1 is null then '(empty)' else col1 end AS name, 
      sum(col3) as col1_sum FROM test GROUP BY col1) a
full outer join (SELECT case when col2 is null then '(empty)' else col2 end AS name, 
      sum(col3) as col2_sum FROM test GROUP BY col2) b
on a.name = b.name
order by a.name;

name | col1_sum | col2_sum | diff
:--- | -------: | -------: | ---:
     |      100 |       43 |  -57
A    |       25 |      115 |   90
B    |        1 |     null |   -1
C    |       33 |        1 |  -32

dbfiddle here

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 10, 2017 at 19:37

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Comments

Scott · Accepted Answer · 2017-05-10 19:38:34Z

First, I'd say that your solution is probably the simplest way to do it given the table structure.

Second, I suggest that you need a different table structure. "Name" should not be scattered around the table in different columns - it needs to be an indexed field. I'd try a table like this:

CREATE TABLE test(
aInstance   int,
aName    text,
col_nam    int,
col_val    int
);

INSERT INTO test VALUES(1,'A',1, 10);
INSERT INTO test VALUES(2,'A',1, 15);
INSERT INTO test VALUES(2,'A',2, 15);
INSERT INTO test VALUES(3,'A',2, 100);
INSERT INTO test VALUES(3,'B',1, 1);
INSERT INTO test VALUES(3,'C',2, 1);
INSERT INTO test VALUES(4,'C',2, 33);

This is less human-readable, but works better in a relational database.

Collectives™ on Stack Overflow

Combine multiple "group by" into single query?

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related