CODEWITHSUNDEEP
sqlsql-server
DHI
DHI

Reputation: 89

How to sum sales by months and compare them

I have a table called SOITEM. In that table the column TOTALPRICE has to be summed and result in the total sales by month, where the column with the dates is called DATELASTFULFILLMENT. I want to compare sales form Jan 2014 with Jan 2015, then Feb 2014 with Feb 2015 and so forth.

I got this so far, but I'm not sure how continue.

Select SUM(SOITEM.TOTALPRICE)
FROM SOITEM
WHERE DATELASTFULFILLMENT>='2014-01-31' AND DATELASTFULFILLMENT<='2014-01-31'

but it only results in totals from Jan 2014.... Thank you.

Upvotes: 0

Views: 89

Answers (4)

mhep
mhep

Reputation: 2139

Or you could do it like this.

WITH MonthTotal AS
(
    SELECT
        DATEADD(MONTH, DATEDIFF(MONTH, 0, DATELASTFULFILLMENT), 0) AS MonthDate
    ,   SUM(TOTALPRICE) AS Total

    FROM
        SOITEM

    GROUP BY
        DATEADD(MONTH, DATEDIFF(MONTH, 0, DATELASTFULFILLMENT), 0)
)

SELECT
    MonthTotal.MonthDate
,   MonthTotal.Total
,   PreviousYear.Total AS PreviousYearTotal

FROM
    MonthTotal
    LEFT JOIN MonthTotal AS PreviousYear
        ON  DATEADD(YEAR, -1, MonthTotal.MonthDate) = PreviousYear.MonthDatee) = PreviousYear.MonthDate

You first group the results based on themonth date, the calculation converts a date to the 1st of the month the date drops in. We then use these results and join back to it getting last years result as well.

Upvotes: 0

Boyd P
Boyd P

Reputation: 434

Use a nested query within your select statement -- notice the subtraction from the YEAR within the nested query to pull back the previous year's summary:

SELECT MONTH(so2.DATELASTFULFILLMENT) AS MonthFulfilled, 
YEAR(so2.DATELASTFULFILLMENT) AS YearFulfilled, 
SUM(so2.TOTALPRICE), 
(SELECT SUM(SOITEM.TOTALPRICE) FROM SOITEM WHERE MONTH(DATELASTFULFILLMENT) = MONTH(so2.DATELASTFULFILLMENT) AND YEAR(DATELASTFULFILLMENT) = (YEAR(so2.DATELASTFULFILLMENT)-1)) AS LastYearTotal
FROM SOITEM AS so2
GROUP BY MONTH(so2.DATELASTFULFILLMENT), YEAR(so2.DATELASTFULFILLMENT)

Upvotes: 0

Rion Williams
Rion Williams

Reputation: 76607

You could consider grouping your results using the Month/Year from your date field and then using calculating the SUM() for each of those groups :

  SELECT DATEPART(Year, DATELASTFULFILLMENT) AS [Year], 
         DATEPART(Month, DATELASTFULFILLMENT) AS [Month], 
         SUM(TOTALPRICE) AS Total
    FROM SOITEM
GROUP BY DATEPART(Year, DATELASTFULFILLMENT), DATEPART(Month, DATELASTFULFILLMENT)
ORDER BY [Year], [Month]

You can see an interactive example of this here and results demonstrated below :

enter image description here

Upvotes: 2

Mojtaba
Mojtaba

Reputation: 5002

This works for MySQL. I assume should be the same for MS SQL.

Select SUM(SOITEM.TOTALPRICE)
FROM SOITEM
WHERE DATELASTFULFILLMENT>='2014-01-31' AND DATELASTFULFILLMENT<='2014-01-31' 
UNION
Select SUM(SOITEM.TOTALPRICE)
FROM SOITEM
WHERE DATELASTFULFILLMENT>='2015-01-31' AND DATELASTFULFILLMENT<='2015-01-31' 
UNION
Select SUM(SOITEM.TOTALPRICE)
FROM SOITEM
WHERE DATELASTFULFILLMENT>='2014-02-31' AND DATELASTFULFILLMENT<='2014-02-31' 
UNION
Select SUM(SOITEM.TOTALPRICE)
FROM SOITEM
WHERE DATELASTFULFILLMENT>='2014-02-31' AND DATELASTFULFILLMENT<='2015-02-31'

Upvotes: 0

Related Questions