How to use the SUM function with Group BY in MySQL?

Question

I have a SQL statement that returns some records depending on some given variables.

Query:

SELECT
     country
    ,(MAX(d_views) - MIN(d_views)) AS viewz 
FROM mv_data
WHERE add_date BETWEEN '2022-01-22' AND '2022-01-22' 
GROUP BY title
ORDER BY viewz DESC;

Output:

country	viewz
NG	14735
NG	12494
GH	6099
GH	5181
GH	3381
NG	3293
GH	2288
NG	169

What I'm looking for is: To use the SUM() function to sum up all the viewz according to their country, something like:

country	viewz
NG	30691
GH	16949

And this is what I did:

SELECT
     country
    ,SUM(MAX(d_views) - MIN(d_views)) AS viewz 
FROM mv_data
WHERE add_date BETWEEN '2022-01-26' AND '2022-01-26'
GROUP BY title
ORDER BY viewz DESC;

But I'm getting this error:

#1111 - Invalid use of group function

I also tried to GROUP BY country without the SUM() function and the result is incorrect:

Query:

SELECT
     country
    ,(MAX(d_views) - MIN(d_views)) AS viewz 
FROM mv_data
WHERE add_date BETWEEN '2022-01-26' AND '2022-01-26'
GROUP BY country
ORDER BY viewz DESC;

Output:

country	viewz
NG	4918377
GH	8185474

What I am doing wrong?

NB: d_views is a field in my table. Depending on a given date, we have a minimum value and a maximum value.

SELECT country, ... GROUP BY title ... ??? really? If definite country value matches one and only one title value and backward then use GROUP BY country. If not then your query is obviously wrong and produces the output which makes no sense. Study ONLY_FULL_GROUP_BY SQL Mode. — Akina
– Akina, Commented Jan 29, 2022 at 18:39

nbk · Accepted Answer · 2022-01-29 19:00:46Z

1

Use the first as subquery for the second like

SELECT
   country
   SUM(viewz) as viewz
FROM (SELECT
     country
    ,MAX(d_views) - MIN(d_views) AS viewz 
FROM mv_data
WHERE add_date BETWEEN '2022-01-26' AND '2022-01-26'
GROUP BY title) t1
GROUP BY country
ORDER BY viewz DESC;

edited Jan 29, 2022 at 19:00

answered Jan 29, 2022 at 18:27

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

1 Comment

Ratty Over a year ago

Thanks so much. This worked perfectly

Enrico Everett · Accepted Answer · 2022-01-29 18:38:32Z

0

if you are looking to use the SUM() function to sum-up all the viewz according to their country just use the following;

SELECT
     country
    ,SUM(d_view) AS viewz 
FROM mv_data
WHERE add_date BETWEEN '2022-01-26' AND '2022-01-26'
GROUP BY country
ORDER BY viewz DESC;

answered Jan 29, 2022 at 18:38

Enrico Everett

1

Collectives™ on Stack Overflow

How to use the SUM function with Group BY in MySQL?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related