Query with count subquery, inner join, and group

Question

I'm definitely noob with SQL, I overloaded my head to write a complex query with the following table structure in Postgresql:

CREATE TABLE reports ( reportid character varying(20) NOT NULL, userid integer NOT NULL, reporttype character varying(40) NOT NULL, ) CREATE TABLE users ( userid serial NOT NULL, username character varying(20) NOT NULL, ) 

The purpose of the query is to get the number of report types for each user and display it in one column. There are three different types of reports.

A simple query with a group solution solves the problem, but displays it on different lines:

 select count(*) as Amount, u.username, r.reporttype from reports r, users u where r.userid=u.userid group by u.username,r.reporttype order by u.username 

Answer 1

 SELECT username, ( SELECT COUNT(*) FROM reports WHERE users.userid = reports.userid && reports.reporttype = 'Type1' ) As Type1, ( SELECT COUNT(*) FROM reports WHERE users.userid = reports.userid && reports.reporttype = 'Type2' ) As Type2, ( SELECT COUNT(*) FROM reports WHERE users.userid = reports.userid && reports.reporttype = 'Type3' ) As Type3 FROM users WHERE EXISTS( SELECT NULL FROM reports WHERE users.userid = reports.userid ) 

Answer 2

 SELECT u.username, COUNT(CASE r.reporttype WHEN 1 THEN 1 END) AS type1Qty, COUNT(CASE r.reporttype WHEN 2 THEN 1 END) AS type2Qty, COUNT(CASE r.reporttype WHEN 3 THEN 1 END) AS type3Qty FROM reports r INNER JOIN users u ON r.userid = u.userid GROUP BY u.username 

If your SQL SQL dialog language requires the ELSE branch to be present in CASE expressions, add ELSE NULL before each END .

Answer 3

If you are looking for “the number of report types for each user,” you expect to see a number equal to 1, 2, or 3 (given that there are three different types of reports) for each user. You will not expect the type of report (it will be considered non-displayable), so you do not need the type of report in terms of the SELECT or GROUP BY query.

Instead, use COUNT (DISTINCT r.reporttype) to count the number of different types of reports used by each user.

 SELECT COUNT(DISTINCT r.reporttype) as Amount ,u.username FROM users u INNER JOIN reports r ON r.userid=u.userid GROUP BY u.username ORDER BY u.username