Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 996
Roberto Betancourt Avatar asked May 02 '11 17:05

Roberto Betancourt


People also ask

Can we use subquery and join together?

A subquery can be used with JOIN operation. In the example below, the subquery actually returns a temporary table which is handled by database server in memory. The temporary table from the subquery is given an alias so that we can refer to it in the outer select statement.

Can you use 2 subqueries in a SQL query?

Any number of subqueries can be nested in a statement.

Is CTE more efficient than subquery?

Advantage of Using CTESince CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion. When you write a query, it is easier to break down a complex query into smaller pieces using CTE.

Can we use subquery in GROUP BY?

The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery. Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.


2 Answers

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
  )
like image 111
Magnus Avatar answered Sep 20 '22 08:09

Magnus


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.

like image 42
Andriy M Avatar answered Sep 23 '22 08:09

Andriy M