Postgresql select items and sum of each of their statuses

Question

I am fed up with creating sql query to database table, which contains, let's say just three columns - id, title and status. What I need is write a select which shows total of all titles and their each and every status sum. Would appreciate any ideas.

Database:

ID	Title	Status
1	Title A	Done
2	Title B	Done
3	Title A	Rejected
4	Title B	Done
5	Title A	Done
6	Title B	Done
7	Title A	Rejected
8	Title B	Done

What I need is:

Title	Done	Rejected
Title A	6	2
Title B	8	0

Thanks in advance.

so you want SO to write the SQL for you, because you are fed up with doing it? — pm100, Mar 05 '22 at 17:26
Well, as I wrote before (in case you are blind), I "Would appreciate any ideas". So, SO, so if anyone knows what functions or methoss on postgres that would help, I would be thankful. — Dainius Java, Mar 06 '22 at 11:49
Well I sure am motivated to help now. I was partially asking if what was needed was SQL. Q2, is it always just those 2 statuses, I mean is it a fixed number? — pm100, Mar 06 '22 at 17:48
It will be fixes number of statuses. For now 5. I am sure that I would construct some how the query I need if someone would put me on the right path. I mean, maybe I should look at COUNT, SUM, or some inner joins between same table - I've been trying for 4 hours with no luck. Some people gives links to readme, some people shares thougts and some write sql to help. I don't think taht write sql statement to someone who is stuck is very HUGE thing, but I see that there are angry people also... — Dainius Java, Mar 06 '22 at 18:04

score 1 · Accepted Answer · answered Mar 06 '22 at 18:29

1

SELECT Title,
   SUM(CASE WHEN Status='Done' THEN 1 ELSE 0 END)  as Done,
   SUM(CASE WHEN Status='Rejected' THEN 1 ELSE 0 END)  as Rejected
   FROM [Table] group by Title

thanks to

Sql Server equivalent of a COUNTIF aggregate function

answered Mar 06 '22 at 18:29

pm100

48,078
23
82
145

Thank you, @pm100, you saved me a lot of nerves for tommorow. God bless you. – Dainius Java Mar 06 '22 at 19:08
Alternatively: `count(*) filter (where status = 'Done')` – Mar 06 '22 at 19:15
@a_horse_with_no_name that doesnt work for me (not using postgres tho) – pm100 Mar 06 '22 at 19:23
Well the question is tagged with Postgres – Mar 06 '22 at 19:26
@a_horse_with_no_name yes, but I was curious if this was standard sql or a postgres special – pm100 Mar 06 '22 at 19:38
The `filter()` clause is part of the ANSI SQL standard. – Mar 06 '22 at 19:42
@a_horse_with_no_name I tried it here https://www.w3schools.com/sql/trysql.asp?filename=trysql_asc dont know what engine that is – pm100 Mar 06 '22 at 19:44
Shrug. w3fools is full of errors anyway. – Mar 06 '22 at 19:48
@a_horse_with_no_name - It was the only sql testbed I had to hand. (like godbolt for testing out code). Can you recommend a better one – pm100 Mar 06 '22 at 19:49
https://dbfiddle.uk/?rdbms=postgres_14 – Mar 06 '22 at 19:50

Postgresql select items and sum of each of their statuses

1 Answers1