CODEWITHSUNDEEP
postgresql
kyle54
kyle54

Reputation: 35

How to combine multiple select statements in postgresql?

I just started learning how to code. I was wondering how to combine multiple SELECT statements in one query.

SELECT count(*) AS "SENT" FROM message_items WHERE status = 'SENT';

SELECT count(*)  AS "NOT SENT" FROM message_items WHERE status = 'NOT SENT';

SELECT count(*) AS "INVALID NUMBER" FROM message_items WHERE status = 'INVALID NUMBER';

Upvotes: 1

Views: 1218

Answers (2)

Lutz
Lutz

Reputation: 655

You can use GROUP BY

SELECT count(*), status FROM message_items GROUP BY status;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

Use conditional aggregation:

SELECT
    COUNT(*) FILTER (WHERE status = 'SENT') AS "SENT",
    COUNT(*) FILTER (WHERE status = 'NOT SET') AS "NOT SENT",
    COUNT(*) FILTER (WHERE status = 'INVALID NUMBER') AS "INVALID NUMBER"
FROM message_items;

If you wanted to generate counts over the entire table, then the above is suitable. If instead you want to generate counts per some other column, then just add that column to the above select clause and then aggregate by that column using GROUP BY.

Upvotes: 3

Related Questions