Mysql SELECT query group by with sum and avg

Question

If I have added "group by date" then sum or avg function is not working.

Here is a table

| date         | calories |
|-------------------------|
| 2021-03-28   | 42.50    |
| 2021-03-30   | 500.00   |
| 2021-03-31   | 35.00    |
| 2021-04-01   | 200.00   |
| 2021-04-01   | 35.00    |

Here is create Query

SELECT CONCAT(round(IF(avg(up.calories), avg(up.calories), 0), 2), "kcal") as avg, CONCAT(round(IF(SUM(up.calories), SUM(up.calories), 0), 2), "kcal") as total_burned
FROM `tbl` as `up`
WHERE `date` BETWEEN "2021-03-28" AND "2021-04-03"
AND `calories` != '0'
GROUP BY `date`

Below is my query result

| avg          | total_burned |
|-----------------------------|
| 42.50        | 42.50        |
| 500.00       | 500.00       |
| 35.00        | 35.00        |
| 235.00       | 235.00       |

But actually, I want to this type of result

| avg          | total_burned |
|-----------------------------|
| 203.13       | 812.50       |

'If I have added "group by date" then sum or avg function is not working.' - then why did you add it , what did you have a problem with that you thought grouping would be a solution? — P.Salmon
– P.Salmon, Commented Apr 1, 2021 at 11:52
@P.Salmon, If I have not added "group by date" then avg is not proper — Pankaj Dhameliya
– Pankaj Dhameliya, Commented Apr 1, 2021 at 12:09
Hi @Pankaj As per your requirement group by is not required. The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns. — Raushan Kumar
– Raushan Kumar, Commented Apr 1, 2021 at 16:37

P.Salmon · Accepted Answer · 2021-04-01 12:52:30Z

1

Roll your own

DROP TABLE IF EXISTS T;
create table t( date date, calories decimal(10,2));
insert into t values
( '2021-03-28'   , 42.50    ),
( '2021-03-30'   , 500.00   ),
( '2021-03-31'   , 35.00    ),
( '2021-04-01'   , 200.00   ),
( '2021-04-01'   , 35.00    );

select sum(calories) sumcal,sum(calories) / count(distinct date) calcavg, avg(calories)
from t;

+--------+------------+---------------+
| sumcal | calcavg    | avg(calories) |
+--------+------------+---------------+
| 812.50 | 203.125000 |    162.500000 |
+--------+------------+---------------+
1 row in set (0.002 sec)

answered Apr 1, 2021 at 12:52

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql SELECT query group by with sum and avg

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related