How to know how many sales for each of the months?

Navigation
-1

I need to set up a query that brings the total sales of each month from a table called sales.SalesOrderHeader , but I'm not getting it. In this table there is a field OrderDate which is the date of sale and TotalDue is the value of the sale. This is my attempt: 

select a.Year, a.Month
from
(
select distinct cast(year(orderdate) as varchar) + '_' +
case when month(OrderDate) < 10 then '0' else '' end + cast(month(OrderDate)as varchar) as Year_Month,
sum(TotalDue) as Total
From sales.SalesOrderHeader
group by month(OrderDate), OrderDate
) a
group by a.Month 
order by 1
    
asked by anonymous 14.08.2017 / 19:34

1 answer

2

The internal query has a Year_Month field and the external query has Year and Month separated. That being the case, I'm going to split the Year_Month into two to stay the exact external query. By doing this separation, we also eliminate the difficulty / complexity of having to deal with months 1-9 as 01-09.

In addition, Total is not used in the external query, but that's what you need there to know the total sales per month.

It is not necessary to group twice, so only GROUP BY is required. The ordering is given chronologically by ORDER BY . 

SELECT a.Year, a.Month, a.Total
FROM (
    SELECT
        YEAR(s.OrderDate) AS Year,
        MONTH(s.OrderDate) AS Month,
        SUM(s.TotalDue) AS Total
    FROM sales.SalesOrderHeader s
    GROUP BY YEAR(s.OrderDate), MONTH(s.OrderDate)
) a
ORDER BY a.Year, a.Month
    
14.08.2017 / 23:33
How do I get the Calendar date on Android? Which Java library that returns the language of the computer? [closed]