Postgresql: Select sum with different conditions

Question

I have two table table:

I. Table 1 like this:

------------------------------------------
codeid | pos   | neg   | category 
-----------------------------------------
   1   | 10    |  3    | begin2016
   1   |  3    |  5    |  justhere
   3   |  7    |  7    |  justthere
   4   |  1    |  1    |  else
   4   | 12    |  0    |  begin2015
   4   |  5    |  12   |  begin2013
   1   |  2    |  50   |  now
   2   |  5    |  33   |  now
   5   |  33   |   0   |  Begin2011
   5   |  11   |   7   |  begin2000

II. Table 2 like this:

------------------------------------------
codeid | codedesc         | codegroupid 
-----------------------------------------
1      |  road runner     |    1
2      |  bike warrior    |    2
3      |  lazy driver     |    4
4      |  clever runner   |    1
5      |  worker          |    3
6      |  smarty          |    1
7      |  sweety          |    3
8      |  sweeper         |    1

I want to have one result like this having two (or more) conditions:

sum pos and neg where codegroupid IN('1', '2', '3')
BUt do not sum pos and neg if category like 'begin%'

So the result will like this:

------------------------------------------
codeid |      codedesc     | sumpos | sumneg
-----------------------------------------
 1   |  roadrunner       |  5     |  55   => (sumpos = 3+2, because 10 have category like 'begin%' so doesn't sum) 
 2   |  bike warrior     |  5     |  33
 4   |  clever runner    |  1     |  1
 5   |  worker           |  0     |  0   => (sumpos=sumneg=0) becase codeid 5 category ilike 'begin%'

Group by codeid, codedesc;
Sumpos is sum(pos) where category NOT ILIKE 'begin%', BUT IF category ILKIE 'begin%' make all pos values become zero (0);
Sumpos is sum(neg) where category NOT ILIKE 'begin%', BUT IF category ILKIE 'begin%' make all neg values become zero;

Any ideas how to do it?

Should the result have records for all codeids (1 - 8)? I thought maybe you only wanted records where codegroupid = 1 or 2, but your result is showing worker (codegroupid = 3). It is not clear if your result set is just showing a subset. — David Aman
– David Aman, Commented Apr 7, 2016 at 1:19
I'm sorry, I have edited coudegroupid to 1 or 2 or 3. Thank you. — bandungeuy
– bandungeuy, Commented Apr 7, 2016 at 1:37

David Aman · Accepted Answer · 2016-04-07 01:49:18Z

1

Try:

SELECT
    b.codeid,
    b.codedesc,
    sum(CASE WHEN category LIKE 'begin%' THEN 0 ELSE a.pos END) AS sumpos,
    sum(CASE WHEN category LIKE 'begin%' THEN 0 ELSE a.neg END) AS sumneg
FROM
    table1 AS a
    JOIN
    table2 AS b ON a.codeid = b.codeid
WHERE b.codegroupid IN (1, 2, 3)
GROUP BY
    b.codeid,
    b.codedesc;

answered Apr 7, 2016 at 1:49

David Aman

3911 silver badge8 bronze badges

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

Collectives™ on Stack Overflow

Postgresql: Select sum with different conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related