CODEWITHSUNDEEP
mysql
turbonerd
turbonerd

Reputation: 1306

Subqueries to SELECT multiple COUNTs from two tables in one query

I'm struggling with what appears to be the most complicated SQL query I've ever written!

Database structure:

frog_shared.staff
`ID`, `Firstname`, `Surname`

frog_observations.observations
`ID`, `Teacher_ID`, `Type`, `Main_Positive_Aspect`, `Main_Development_Aspect`, `Grade_for_Behaviour`, `Grade_for_Attainment`, `Grade_for_Teaching`

frog_observations.aspects
`ID`, `Observation_ID`, `Label_ID`, `Type`

frog_observations.aspect_labels
`ID`, `Title`

Sample data:

Member of staff:
`12345`, `Duncan`, `Wraight`

Observation:
`9888`, `12345`, `Formal`, `5`, `7`, `1`, `1`, `1`

Aspects:
`101`, `9888`, `2`, `P`

Aspect labels:
`2`, `Questioning`

What I'm trying to achieve:

I'd like to produce a list of our best teachers in a specific observation aspect. For example, I'd like to see my top 5 teachers for "Questioning".

On top of that, I'd like to filter the data somewhat:

My attempts:

This statement filters the observations by their grades, but doesn't include any main positive aspects (they weren't included in the original system)

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS `Teacher`,
  COUNT( * ) AS Total,
  GREATEST(o.`Achievement_Grade`, o.`Behaviour_Grade`, o.`Teaching_Grade`) AS `Worst Grade`
FROM frog_observations.observations o
  INNER JOIN frog_shared.staff s ON o.Teacher_ID = s.ID
  INNER JOIN frog_observations.aspects a ON o.ID = a.Observation_ID
WHERE a.Aspect_ID =  4
  AND ( GREATEST(o.`Achievement_Grade`, o.`Behaviour_Grade`, o.`Teaching_Grade`) BETWEEN 1 AND 2 ) AND o.Datetime > '2011-09-01'
  AND a.Type =  'P'
GROUP BY s.ID
ORDER BY `Total` DESC 
LIMIT 5

Now the way I'd like to do it is something like this:

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS `Teacher`,
  COUNT(mp.*) AS `Number of Appraisal Main Positives`,
  COUNT(sp.*) AS `Number of SBP Main Positives`,
  COUNT(a.*) AS `Number of Other Positives`,
  SUM(`Number of Appraisal Main Positives` + `Number of SBP Main Positives` `Number of Other Positives`) AS `Total`
FROM
  `frog_observations`.`observations` o,
  ( `frog_observations`.`observations` WHERE `Type` = 'Formal') AS mp,
  ( `frog_observations`.`observations` WHERE `Type` = 'SBP') AS sp
INNER JOIN
  `frog_observations`.`aspects` a ON a.Observation_ID = o.ID
GROUP BY s.ID

Unfortunately, other than knowing the headings I want (i.e. number of appraisal main positives, number of sharing best practice positives, number of standard positives and a weighted total), I have no idea how to write what I presume will be subqueries to get all of this information from a single statement.

Any guidance appreciated.

Edit: Sample input/output

Input User wants to view Top 5 members of staff for the aspect Questioning

Process - Aspect Questioning has ID 4 in the aspect_labels table. - System should then, for each member of staff, count the observations they have with Main_Positive_Aspect set to 4. Some of these observations will be of Type Formal and some will be of Type SBP. - System should also then count the number of rows in the aspects table for each member of staff where the Aspect_ID is 4.

Output

Key:

Example output:

-------------------------------------------------------------
|       Staff Name      FMP     SMP     Other       Points  |
|------------------------------------------------------------
|       D Wraight       2       1       4           12      |
|       A Nother        3       0       0           9       |
|       J Bloggs        0       4       1           9       |
|       J Arthur        1       1       1           6       |
|       M Turner        0       1       0           2       |
-------------------------------------------------------------

Here are the queries, written individually, which work on my database. Basically I need to amalgamate these into one query. If that's possible!

#=======#
#  FMP  #
#=======#

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS `Teacher`,
  COUNT( * ) AS `FMP`
FROM frog_observations.observations o
  INNER JOIN frog_shared.staff s ON o.Teacher_ID = s.ID
WHERE o.Main_Positive  =  4
  AND o.Type = 'F'
  AND ( GREATEST(o.`Achievement_Grade`, o.`Behaviour_Grade`, o.`Teaching_Grade`) BETWEEN 1 AND 2 )
GROUP BY s.ID

#=======#
#  SMP  #
#=======#

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS `Teacher`,
  COUNT( * ) AS `SMP`
FROM frog_observations.observations o
  INNER JOIN frog_shared.staff s ON o.Teacher_ID = s.ID
WHERE o.Main_Positive  =  4
  AND o.Type = 'S'
GROUP BY s.ID

#=========#
#  Other  #
#=========#

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS `Teacher`,
  COUNT( * ) AS `Other`
FROM observations o
  INNER JOIN frog_shared.staff s ON o.Teacher_ID = s.ID
  INNER JOIN aspects a ON o.ID = a.Observation_ID
WHERE a.Aspect_ID = 4
  AND a.Type = 'P'
GROUP BY s.ID

Upvotes: 0

Views: 1211

Answers (1)

Steph Locke
Steph Locke

Reputation: 6146

Would something like this work for the initial cut...

SELECT
  CONCAT(s.Firstname, " ", s.Surname) AS Teacher,
  sum(Case when `Type` = 'Formal' then 1 else 0 end) AS `Number of Appraisal Main Positives`,
  sum(Case when `Type` = 'SBP' then 1 else 0 end)  AS `Number of SBP Main Positives`,
  coalesce(OtherPositives,0) AS `Number of Other Positives`,
  sum(Case when `Type` in ( 'Formal' ,'SBP') then 1 else 0 end) + coalesce(OtherPositives,0)  AS `Total`
FROM observations o
INNER JOIN staff s ON o.Teacher_ID = s.ID
LEFT JOIN (select Observation_ID, count(*) as OtherPositives) from aspects where Label_ID=4 group by Observation_ID) a ON a.Observation_ID = o.ID
WHERE Main_Positive =4
GROUP BY s.ID

Upvotes: 2

Related Questions