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

MySQL

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;

RESULT

WHAT I AM LOOKING FOR: I want to be able to use the SUM() function to sum-up all the viewz according to their country Something like this;

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;

THIS IS THE ERROR I GOT:

#1111 - Invalid use of group function

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

CODE:

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;

RESULT:

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.

CodePudding user response：

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;

CodePudding user response：

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;