CODEWITHSUNDEEP
sqlpostgresqlselectcount
robert_gonzalez
robert_gonzalez

Reputation: 27

PostgreSQL select and count column from table on different conditions

I've got a table (tb_author) that shows info about texts and authors. It would look like this:

AUTHOR | TEXTID | APPROVED
A1       T1       OK
A1       T2       OK
A2       T3       NOK
A2       T4       OK
A2       T5       OK
A3       T6       OK
A3       T7       NOK
A3       T8       NOK
A3       T9       OK
A4       T10      OK

I would need to count the info here in 2 different ways. First, see how many articles has each author written, and second, see how many articles has each author approved.

I just know how to do separate counts for every query, but I don't know how to display them in a view that looks like this:

AUTHOR | TEXTS| TEXTSAPPROVED
A1       2            2
A2       3            2
A3       4            2
A4       1            1

With my very limited knowledge I just get to do two separate queries:

SELECT author, COUNT(*) 
FROM tb_author
GROUP BY author

SELECT author, COUNT(*)
FROM tb_author
WHERE approved='OK'
GROUP BY author

Thank you all!

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Use conditional aggregation with the filter clause:

SELECT author, COUNT(*),
       COUNT(*) FILTER (WHERE approved = 'OK')
FROM tb_author
GROUP BY author

Upvotes: 1

Related Questions