CODEWITHSUNDEEP
mysqlsql
Lennart
Lennart

Reputation: 1028

Group by count()

I'm trying to make the following query work:

SELECT
    DATE_FORMAT( date, '%Y %m' ) AS `Month`, 
    COUNT( schedule_id ) AS `Shifts`,
    COUNT(user_id) AS `Users`
FROM 
    schedule
GROUP BY 
    `Month`, `Shifts`

It should give a frequency table stating how many users work a certain amount of shifts, per month (e.g. in Dec. there were 10 users working 20 shifts, 12 users working 15 shifts etc).

MySQL can't group on a COUNT() though, so the query breaks. How can I make this work?

Upvotes: 1

Views: 4717

Answers (3)

Krzysztof
Krzysztof

Reputation: 16130

Try this:

SELECT
    `Month`, `Shifts`, COUNT(`User`) `Users`
FROM (  
    SELECT -- select nr of shifts per user
        DATE_FORMAT( date, '%Y %m' ) AS `Month`, 
        user_id AS `User`,
        COUNT( schedule_id ) AS `Shifts`
    FROM 
        schedule
    GROUP BY 
        `Month`, `User`
) s
GROUP BY `Month`, `Shifts`

Inner query returns month, user and shifts count. In outer query you can group by shifts.

Upvotes: 2

SergeS
SergeS

Reputation: 11779

Use subquery to get counts per some idetifier ( column id in example ), then join it with original query

SELECT ... FROM schedule sh JOIN ( SELECT id, COUNT( schedule_id ) AS Shifts FROM schedule ) AS cnt ON cnt.id = sh.id GROUP BY ..., cnt.Shifts

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

SELECT
    y
  , m
  , Shifts
  , COUNT(*) AS Users 
FROM
  ( SELECT
        YEAR(date) AS y
      , MONTH(date) AS m
      , user_id 
      , COUNT(*) AS Shifts
    FROM 
        schedule
    GROUP BY 
        YEAR(date), MONTH(date), user_id 
  ) AS grp
GROUP BY
    y
  , m
  , Shifts

Upvotes: 0

Related Questions