CODEWITHSUNDEEP
mysqlsqlgroup-by
Ratty
Ratty

Reputation: 13

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

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.

Upvotes: 0

Views: 121

Answers (2)

nbk
nbk

Reputation: 49375

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;

Upvotes: 1

Enrico Everett
Enrico Everett

Reputation: 1

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;

Upvotes: 0

Related Questions