How to know how many sales for each month?

Asked

Viewed 708 times

-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
sql sql-server tsql
  • 1

    Your question is misspelled. Please show the table structures so we can help you better.

  • What is the structure of the tables?

  • From hitting the eye, it seems that missing select in the external query the column Total defined in the subquery.

  • order by 1 - What is the purpose of this?

1 answer

2

Internal consultation has a field Year_Month and the external has Year and Month separate. So I will divide the Year_Month in two to stay as the external consultation needs. Doing this separation also eliminates the difficulty/complexity of having to deal with the months 1-9 as being 01-09.

In addition, the Total is not used in external consultation, but this is what you need there to know the total of sales per month.

It is not necessary to group twice, so only one GROUP BY is necessary. The ordering is given chronologically by the 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

Browser other questions tagged sql sql-server tsql

You are not signed in. Login or sign up in order to post.