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

There are only three different types of reports: "type1" "type2" "type3" and the query should display in one row the username, the amount of "type1" reports, the amount of "type2" reports and the amount of "type3" reports. Sorry for the confusion — Roberto Betancourt, May 02 '11 at 18:22
In the query in your example you use c.userid and c.reporttype but where's that c coming from? You haven't made any alias named c or a table named c. — Ruben, May 02 '11 at 18:55

Magnus · Accepted Answer · 2011-05-02T19:44:53.013

28

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
  )

edited May 02 '11 at 19:44

answered May 02 '11 at 19:23

Magnus

45,362
8
80
118

That did the trick pretty well but is there a way to disregard users with zero reports? – Roberto Betancourt May 02 '11 at 19:33
You don't need to update your question, just mark the correct answer as the solution. – Magnus May 02 '11 at 19:58

score 13 · Answer 2 · answered May 02 '11 at 19:35

13

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.

answered May 02 '11 at 19:35

Andriy M

76,112
17
94
154

I upvote because I am not a bigfan of subquery. Thank you. – Prabowo Murti Dec 08 '17 at 11:46
1

I find this so much easier to read and understand than the subquery, is there a performance difference between the two? – mapping dom Apr 17 '19 at 11:30
@mappingdom: I would expect this one to be faster than the subquery solution. With that solution you hit the `reports` table once per each count and once more for the `EXISTS` check, whereas if you use a join like here, each table will be touched only once. – Andriy M Apr 17 '19 at 20:19

score 0 · Answer 3 · answered May 02 '11 at 17:52

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

That wasnt quite what i was looking for. There are only three different types of reports: "type1" "type2" "type3" and the query should display in one row the username, the amount of "type1" reports, the amount of "type2" reports and the amount of "type3" reports. Sorry for the confusion. — Roberto Betancourt, May 02 '11 at 18:20

query with count subquery, inner join and group

3 Answers3

Linked