Group rows into two types depending on a value in column

Question

I have a table:

------------------------------------------
Uid | mount | category 
-----------------------------------------
1   | 10    |    a
1   |  3    |    b
3   |  7    |    a
4   |  1    |    b
4   | 12    |    a
4   |  5    |    b
1   |  2    |    c
2   |  5    |    d

I want to have one result like this:

------------------------------------------
Uid | suma | sumnota
-----------------------------------------
 1  |  10  |    5    
 2  |   0  |    5
 3  |   7  |    0
 4  |  12  |    6

Group by uid;
Suma is sum(mount) where catagory = 'a';
Sumnota is sum(mount) where catagory <> 'a';

Any ideas how to do it?

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Use conditional aggregation with CASE statements in SUM() function:

SELECT
    uid
  , SUM(CASE WHEN category = 'a' THEN mount ELSE 0 END) AS suma
  , SUM(CASE WHEN category IS DISTINCT FROM 'a' THEN mount ELSE 0 END) AS sumnota
FROM
  yourtable
GROUP BY uid
ORDER BY uid

I'm using IS DISTINCT FROM clause to properly handle NULL values in category column. If that's not your case you could simply use <> operator.

From documentation (bold emphasis mine):

Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null.

For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns false, and if only one input is null it returns true.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 05 '16 at 23:03

Kamil Gosciminski

16,547
8
49
72

Thank you! It's work! I have another case similar with this problem. The tables are two, joining this two table with same problem. But, later if I could'nt solved by my self, I will ask again. Thanks! – bandungeuy Apr 05 '16 at 23:20
Approach doesn't change. Figure out your grouping, your cases and what your calculated (in this case summed) column is. – Kamil Gosciminski Apr 05 '16 at 23:31

Luc M · Answer 2 · 2016-04-06T02:13:33.780

Here's a solution more "verbosed" than accepted answer.

   WITH
      t_suma AS    ( SELECT uid, SUM(mount) AS suma
                     FROM your_table  
                     WHERE category = 'a' 
                     GROUP BY uid ),
      t_sumnota AS ( SELECT uid, SUM(mount) AS sumnota 
                     FROM your_table 
                     WHERE category <> 'a' or category is NULL
                     GROUP BY uid )
    SELECT distinct y.uid, COALESCE( suma, 0) AS suma, COALESCE( sumnota, 0 ) AS sumnota
    FROM your_table y LEFT OUTER JOIN t_suma    ON ( y.uid = t_suma.uid )
                      LEFT OUTER JOIN t_sumnota ON ( y.uid = t_sumnota.uid ) 
    ORDER BY uid;

Group rows into two types depending on a value in column

2 Answers2