How to formulate a conditional sum in PostgreSQL?

Question

I have a table containing id, category, noofquestions and company. I want a query which would return the noofquestions as sum of the values of noofquestions when category is same in two or more columns. I'm trying this query but it is only adding those columns whose category is same and noofquestions are equal which is wrong. It should not check for noofquestions.

 SELECT id ,  category,   SUM(NULLIF(noofquestions, '')::int), company 
 FROM tableName   
 WHERE id=1 
 GROUP BY id, category, noofquestions, company;

"not working properly"? Um. Edited to provide meaningful title. — Craig Ringer
– Craig Ringer, Commented Dec 23, 2013 at 9:11

Mureinik · Accepted Answer · 2013-12-23 09:02:47Z

2

You should not group by noofquestions:

SELECT   id, category, SUM(NULLIF(noofquestions, '')::int), company 
FROM     tableName 
WHERE    id = 1 
GROUP BY id, category, company;

answered Dec 23, 2013 at 9:02

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

3 Comments

v0ld3m0rt Over a year ago

this is working, but explain the behavior a little if you may.

Milen Over a year ago

Aggregate functions like SUM, AVG, MIN and MAX on a table column require the table column in question to be excluded from the GROUP BY clause in order to achieve the desired results.

GlennS Over a year ago

If you use GROUP BY, then you return one row for each combination of the things in your GROUP BY statement. Any aggregations (like SUM) you put into the select are then used to turn many rows from your original table into a single row in your result. So, if you want to sum together rows with different values of noofquestions, it can't be in your GROUP BY statement.

Collectives™ on Stack Overflow

How to formulate a conditional sum in PostgreSQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related