sql command to find out how many players score how much-CodePudding

I have a table like these

country	gender	player	score	year	ID
Germany	male	Michael	14	1990	1
Austria	male	Simon	13	1990	2
Germany	female	Mila	16	1990	3
Austria	female	Simona	15	1990	4

This is a table in the database. It shows 70 countries around the world with player names and gender. It shows which player score how many goals in which year. The years goes from 1990 to 2015. So the table is large. Now I would like to know how many goals all female player and how many male player from Germany have scored from 2010 to 2015. So I would like to know the total score of german male player and the total score of german female player every year from 2010 to 2015 with a Sqlite

I expecting these output

country	gender	score	year
Germany	male	114	2010
Germany	female	113	2010
Germany	male	110	2011
Germany	female	111	2011
Germany	male	119	2012
Germany	female	114	2012
Germany	male	119	2013
Germany	female	114	2013
Germany	male	129	2014
Germany	female	103	2014
Germany	male	109	2015
Germany	female	104	2015

CodePudding user response：

SELECT 
    country,
    gender,
    year,
    SUM(score) AS score
FROM
    <table_name>
WHERE
    country ='Germany' 
    AND year between 2010 and 2015
GROUP BY 
    1, 2, 3

filtering on country and the years you are interested in
then summing up total score using group by