Remove Duplicates and show Total sales by year and month-CodePudding

i am trying to work with this query to produce a list of all 11 years and 12 months within the years with the sales data for each month. Any suggestions? this is my query so far.

SELECT 
     distinct(extract(year from date)) as year
     , sum(sale_dollars) as year_sales
from `project-1-349215.Dataset.sales`
group by date

it just creates a long list of over 2000 results when i am expecting 132 max one for each month in the years.

CodePudding user response：

You should change your group by statement if you have more results than you expected.

You can try:

group by YEAR(date), MONTH(date)

group by EXTRACT(YEAR_MONTH FROM date)

CodePudding user response：

A Grouping function is for takes a subsection of the date in your case year and moth and collect all rows that fit, and sum it up,

So a s´GROUp BY date makes no sense, what so ever as you don't want the sum of every day

So make this

SELECT 
     extract(year from date) as year
     ,extract(MONTH from date) as month
     , sum(sale_dollars) as year_sales
from `project-1-349215.Dataset.sales`
group by 1,2

Or you can combine both year and month

SELECT 
     extract(YEAR_MONTH from date) as year
     , sum(sale_dollars) as year_sales
from `project-1-349215.Dataset.sales`
group by 1