query with count subquery, inner join and group

Question

I'm definitely a noob with SQL, I've been busting 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 objective of the query is to fetch the amount of report types per user and display it in one column. There are three different types of reports.

A simple query with group-by will solve the problem but display it in different rows:

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

If you're looking for the "amountof report types per user", you'll be expecting to see a number, either 1, 2 or 3 (given that there are three different types of reports) against each user. You won't be expecting the reporttype (it'll just be counted not displayed), so you don't need reporttype in either the SELECT or the GROUP BY part of the query.

Instead, use COUNT(DISTINCT r.reporttype) to count the number of different reporttypes that are 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

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 server's SQL dialect requires the ELSE branch to be present in CASE expressions, add ELSE NULL before every END.

Answer 3

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
  )