In my SQL table I have "country" and "we200326" columns. Column "we200326" contains only "1" or "NULL" entries.
I'm trying to get a total of all "1"s in column "we200326" and a total by country. I have written the following statement but it gives an error but I don't know what I did wrong (I'm very new at this):
SELECT country, we200326,
(SUM(we200326) OVER () AS Total)
(SUM(we200326) OVER (PARTITION BY country) AS CountryTotal)
FROM table_name
ORDER BY CountryTotal, Country;
The error I get is this:
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OVER () AS Total)
(SUM(we200326) OVER (PARTITION BY country) AS CountryTotal)
' at line 2
I have searched for similar errors and found several (each time was a simple syntax error like a space or comma or so) I tried several versions but could not resolve my problem when following those instructions. Any help would be appreciated.
Window functions are available in MySQL 8.0 only.
In earlier versions, one option is to use subqueries:
select
country,
wewe200326,
(select sum(we200326) from table_name) total,
(select sum(we200326) from table_name t1 where t1.country = t.country) country_total
from table_name t
order by country_total, country