CODEWITHSUNDEEP
mysql
John Lang
John Lang

Reputation: 125

MYSQL COUNT results and AVG them while grouping by a column

I am trying to select the average number of completions per day for the past 30 days and then average that. Here is an example of my Rundown table

RundownID | WorkOrderID | ForemanID | Completion | RundownDate
1         | 1           | 1         | 1          | 2017-10-27
2         | 2           | 1         | 1          | 2017-10-26
3         | 3           | 1         | 1          | 2017-10-20
4         | 4           | 1         | 1          | 2017-10-27
5         | 5           | 2         | 1          | 2017-10-27
6         | 6           | 2         | 1          | 2017-10-27
7         | 7           | 3         | 1          | 2017-10-25
8         | 8           | 2         | 0          | 2017-10-20

The results I am looking for is

ForemanID | ForemanAvg
1         | 4
2         | 2
3         | 1

It should take the total number of Completion rows in the last 30 days and average the total per day for each unique ForemanID. Then average the total per day to give me the average per last 30 days

I know I am close, but I just can't seem to get it configured right. Here is how far I got

SELECT
    ForemanID,
    COUNT(RundownDate) AS ForemanAvg
FROM Rundown
WHERE RundownDate > (DATE_SUB(CURDATE(), INTERVAL '30' Day))
AND Completion = 1
GROUP BY ForemanID

RESULTS

ForemanID | ForemanAvg
23        | 70
24        | 100
25        | 100
26        | 76
27        | 109
29        | 11

EDIT:

I am getting closer. Using the following query I am getting the result I want, however it only gives me one ForemanID for some reason

SELECT 
    a.ForemanID,
    AVG(a.ForemanAvg)
FROM (SELECT
    RundownDate,
    ForemanID,
    COUNT(RundownDate) AS ForemanAvg
FROM Rundown
WHERE RundownDate > (DATE_SUB(CURDATE(), INTERVAL '30' Day))
AND Completion = 1
GROUP BY ForemanID, RundownDate ) AS a

Upvotes: 3

Views: 44

Answers (2)

Racil Hilan
Racil Hilan

Reputation: 25361

If you want to "average the total per day for each unique ForemanID", then you need to add the date to the grouping:

SELECT
    ForemanID,
    COUNT(Completion) AS ForemanAvg
FROM Rundown
WHERE RundownDate > (DATE_SUB(CURDATE(), INTERVAL '30' Day))
AND Completion = 1
GROUP BY ForemanID, RundownDate

Note: This is not called "average", this is a count. If you really want the average, then use AVG(Completion) instead of COUNT(Completion).

EDIT To add an average row in addition to the daily total, you can do that in another query and use UNION to combine the two queries.

SELECT
    ForemanID,
    COUNT(Completion) AS ForemanAvg
FROM Rundown
WHERE RundownDate > (DATE_SUB(CURDATE(), INTERVAL '30' Day))
AND Completion = 1
GROUP BY ForemanID, RundownDate 
UNION
SELECT
    ForemanID,
    AVG(Completion) AS ForemanAvg
FROM Rundown
WHERE RundownDate > (DATE_SUB(CURDATE(), INTERVAL '30' Day))
AND Completion = 1
GROUP BY ForemanID
ORDER BY ForemanID

Upvotes: 1

Harshil Doshi
Harshil Doshi

Reputation: 3592

Following query should work:

SELECT
    ForemanID,
    count(*)/COUNT(distinct RundownDate) AS ForemanAvg
FROM Rundown
WHERE RundownDate > (DATE_SUB(CURDATE(), INTERVAL '30' Day))
AND Completion = 1
GROUP BY ForemanID;

Hope it helps!

Upvotes: 2

Related Questions