CODEWITHSUNDEEP
mysqlgroup-bysumaggregate-functionsaverage
John919
John919

Reputation: 13

How to select the Average of a Sum column in the same result

I have a table of operations with dates and quantities.

dateTime quantity
2024-01-01 1
2024-02-5 2

I want to sum the quantities for each month and then group by each month, which is working fine.

Then i also want to average the sums above (Average over all quantities in the current year) and display the final result in one table.

Month SUM(quantity) AVG(XXX)
01 1860 1648
02 1899 1648
03 1677 1648
04 2166 1648
05 1576 1648
06 1718 1648
07 2144 1648
08 872 1648
09 924 1648 
SELECT MONTH(o1.dateTime), SUM(o1.quantity)
FROM operation o1
WHERE o1.dateTime between '2024-01-01' and '2024-12-31'
GROUP BY MONTH(o1.dateTime)

Upvotes: -1

Views: 55

Answers (1)

ArtBindu
ArtBindu

Reputation: 2016

Use any one of query:

SELECT 
    MONTH(o1.dateTime) AS MONTH, 
    -- COUNT(MONTH(o1.dateTime)) as COUNT,
    SUM(o1.quantity) AS SUM,
    ROUND(SUM(o1.quantity)/COUNT(MONTH(o1.dateTime)), 2) AS AVG
FROM operation o1
WHERE o1.dateTime between '2024-01-01' and '2024-12-31'
GROUP BY MONTH(o1.dateTime)

OR

SELECT 
    MONTH(o1.dateTime) AS MONTH, 
    -- COUNT(MONTH(o1.dateTime)) as COUNT,
    SUM(o1.quantity) AS SUM,
    ROUND(AVG(o1.quantity), 2) AS AVG
FROM operation o1
WHERE o1.dateTime between '2024-01-01' and '2024-12-31'
GROUP BY MONTH(o1.dateTime)

db<>fiddle

Upvotes: 0

Related Questions