CODEWITHSUNDEEP
sqlpostgresqlansi-sql
sgelves
sgelves

Reputation: 134

Changing a query on Postgres to use only the Standard SQL Clauses

I did a query on Postgres and I used the FILTER clause which is available in Postgres 9.4 or greater, but I was told that I could only use standard SQL Clauses.

My query is about counting how many animals have been slaughtered, the amount of slaughtered animals is going to be shown for each enterprise and the animal type:

Enterprise(id or name) || cow || sheep || chicken
MacDonals              ||  5  ||  5  ||    1     
Burguer King           ||  7  ||  4  ||    2     
KFC                    ||  1  ||  0  ||    10

So I made this native query on POSTGRES 9.4:

SELECT e.name
,COALESCE (COUNT(a.id) FILTER (WHERE a.type='cow'))
,COALESCE (COUNT(a.id) FILTER (WHERE a.type='sheep'))
,COALESCE (COUNT(a.id) FILTER (WHERE a.type='chicken'))
FROM enterprise e
INNER JOIN animal_enterprise ae
ON ( ae.enterprise_id = e.id)
INNER JOIN animal a
ON ( ae.animal_id=a.id )
GROUP BY e.id

So, I've tried doing subqueries for each type of animal but it is not as good as it should.

Upvotes: 1

Views: 63

Answers (1)

sstan
sstan

Reputation: 36493

If I understand correctly how filter works, this can be translated by using a case expression inside the count function:

SELECT e.name
  ,COUNT(case when a.type='cow' then 'X' end) as cow
  ,COUNT(case when a.type='sheep' then 'X' end) as sheep
  ,COUNT(case when a.type='chicken' then 'X' end) as chicken
FROM enterprise e
INNER JOIN animal_enterprise ae
  ON ae.enterprise_id = e.id
INNER JOIN animal a
  ON ae.animal_id = a.id
GROUP BY e.id, e.name

I ignored a few anomalies in your query, like the use of p.id?? when there is no alias p defined anywhere, or the use of coalesce without a second parameter.

Upvotes: 2

Related Questions