dustedrob dustedrob - 5 months ago 8
SQL Question

query with count subquery, inner join and group

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
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
  )
Comments