CODEWITHSUNDEEP
sqlpostgresqlselect
Peter
Peter

Reputation: 181

multiple select with aggregates on the same table

I have been trying to get this done with unions, joins on the same table and nested selects but I just cannot get the result I need.

So, to start: all the data I need is in table1, which short version structure is like this:

ownerID varchar(25)
supervisorID varchar(25)
statusID int

Sample data is:

john.smith | joe.blogs | 1
john.smith | joe.blogs | 1
john.smith | joe.blogs | 1
john.smith | joe.blogs | 2
jane.doe   | joe.blogs | 1
jane.doe   | joe.blogs | 1
jane.doe   | joe.blogs | 1
jane.doe   | joe.blogs | 1
billy.bob  | mary.jane | 1
billy.bob  | mary.jane | 2

What I wish to get, in a pseudo-sql would be:

SELECT ownerID, COUNT(*) as CountOne, COUNT(*) as CountTwo
FROM table1
WHERE supervisorID='joe.blogs'AND statusID=1
OR supervisorID='joe.blogs'AND statusID=2
GROUP BY ownerID

producing the below result:

  userID   | countOne | countTwo
----------------------------------
john.smith |     3    |    1
----------------------------------
jane.doe   |     4    |    0

obviously the pseudo-SQL has no right to work, but like I said I tried with UNION, JOIN on the same table, nested Selects, and ask google but nothing I produced would give me required result :(

The DB is using PostreSql.

Upvotes: 1

Views: 50

Answers (2)

Horia
Horia

Reputation: 1612

You can use SUM instead of COUNT and sum only values that are matching the statusId you need - for CountOne -> Status = 1 and for CountTwo -> Status = 2:

SELECT ownerId, 
        SUM(CASE WHEN statusId = 1 THEN 1 ELSE 0 END),
        SUM(CASE WHEN statusId = 2 THEN 1 ELSE 0 END)
FROM    table1
WHERE   supervisorId = 'joe.blogs'
GROUP BY ownerId

Hope it helps.

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Try this

select ownerID , 
sum(case when statusID =1 then 1 else 0 end) as countOne,
sum(case when statusID =2 then 1 else 0 end) as countTwo
from table1
group by ownerID

Upvotes: 0

Related Questions