CODEWITHSUNDEEP
mysqlsqljoincount
amburnside
amburnside

Reputation: 1903

MYSQL Count Query across multiple tables

I need to produce a table of results based on data across four tables.

These tables are 'users', 'departments', 'report_info', 'report_eval'. Basically a report is created about a user and is stored along with some basic info about the report in 'report_info' eg:

report_info:

report_id
uid
report_date
report_title
...

When the user report is evaluated it is save in 'report_eval'

report_eval:

id
report_id
report
grade

A grade for a report can be one of four letter A,B,C,D

Each user belongs to a department. The user's table looks something like:

users:

uid
forename
surname
dept_id
...

The department table looks like this:

dept_id
dept_name

I need to produce a list of results showing amount grades at each level for all departments at each level. An example would be:

Department | A | B | C | D 
----------------------------
Finance    | 0 | 1 | 0 | 3
Sales      | 6 | 2 | 3 | 1
Admin      | 0 | 0 | 0 | 0
...

etc.

So far my query is as follows, but is not giving the desired result:

SELECT 
  d.dept_id, 
  d.dept_name, 
  COUNT(NULLIF(re.grade, 'A')) AS gradeA, 
  COUNT(NULLIF(re.grade, 'B')) AS gradeB, 
  COUNT(NULLIF(re.grade, 'C')) AS gradeC, 
  COUNT(NULLIF(re.grade, 'D')) AS gradeD
FROM report_eval re 
JOIN report_info ri ON ri.report_id=re.report_id 
JOIN users u ON u.uid=ri.uid
RIGHT JOIN departments d ON d.dept_id=u.uid 
GROUP BY d.dept_id

My query results in all departments being listed, however, all grade counts are set to zero.

Hope this all makes sense. Can anyone can adapt my query to point me in the right direction.

All help is greatly appreciated. Thanks in advance.

EDIT 1

SQL Fiddle as requested by @peterm:

http://sqlfiddle.com/#!2/c1f81/2

EDIT 2

Just realised; @Meherzad's answer does not work fully if there are no values for a department then it does not list them. Sorry if this was not clear from the initial question, but I need to show null values.

EDIT 3 - Answer An adaption of @Meherzad's answer and my initial query produced the correct result. Credit to @Meherzad. Here is the latest SQL Fiddle: http://sqlfiddle.com/#!2/73015/1

Upvotes: 0

Views: 309

Answers (2)

Mikhail
Mikhail

Reputation: 1560

I don't suppose this is the best solution, but it's the most obvious one. Just add a query with all zeroes for all departments. Taking the @Meherzad query, should be something like this:

SELECT res.dept_Name, SUM(res.A) as 'A', SUM(res.B) as 'B', SUM(res.C) as 'C', SUM(res.D) as 'D'
FROM
(SELECT dept_Name, 0 As 'A', 0 As 'B', 0 As 'C', 0 As 'D'
FROM departments
UNION ALL
(SELECT 
   d.dept_Name,
   sum(if(re.grade='A', 1, 0)) as 'A',
   sum(if(re.grade='B', 1, 0)) as 'B',
   sum(if(re.grade='C', 1, 0)) as 'C',
   sum(if(re.grade='D', 1, 0)) as 'D'
FROM 
   departments d
INNER JOIN 
   users u
ON 
   u.dept_id=d.dept_id
INNER JOIN
   report_info ri
ON 
   ri.uid=u.uid
INNER JOIN 
   report_eval re
ON
   re.report_id=ri.report_id
GROUP BY 
   d.dept_Name)) res
GROUP BY res.dept_name

Upvotes: 0

Meherzad
Meherzad

Reputation: 8553

Try this query 

SELECT 
   d.dept_Name,
   sum(if(re.grade='A', 1, 0)) as 'A',
   sum(if(re.grade='B', 1, 0)) as 'B',
   sum(if(re.grade='C', 1, 0)) as 'C',
   sum(if(re.grade='D', 1, 0)) as 'D'
FROM 
   dept d
INNER JOIN 
   users u
ON 
   u.dept_id=d.dept_id
INNER JOIN
   report_info ri
ON 
   ri.uid=u.uid
INNER JOIN 
   report_eval re
ON
   re.report_id=ri.report_id
GROUP BY 
   d.dept_Name

Upvotes: 2

Related Questions