CODEWITHSUNDEEP
sqlsql-serversum
Hotlansy Httlandy
Hotlansy Httlandy

Reputation: 349

SQL Server Adding summing values based on the month

I have a table that has the following 2 columns: Date and Price

I want to sum the prices based on the same month of the same year. This is what I have:

2012-07-27 00:00:00.000 0
2012-07-27 00:00:00.000 15000
2012-08-27 00:00:00.000 0
2012-08-27 00:00:00.000 12000
2012-09-28 00:00:00.000 1000
2012-09-28 00:00:00.000 9000
2012-10-26 00:00:00.000 0

I want the following:

2012-07-27 00:00:00.000 15000
2012-08-27 00:00:00.000 12000
2012-09-28 00:00:00.000 10000
2012-10-26 00:00:00.000 0

Thank you.

Upvotes: 1

Views: 4356

Answers (3)

Hart CO
Hart CO

Reputation: 34774

You could get your desired result given your sample data and output with:

SELECT DATE, SUM(Price)
FROM YourTable
GROUP BY DATE
ORDER BY DATE

If you had multiple dates in a month and wanted to return each distinct date but still show the monthly sum for each record you could use:

SELECT DATE, SUM(SUM(Price)) OVER (PARTITION BY YEAR(Date),MONTH(Date))
FROM YourTable
GROUP BY DATE
ORDER BY DATE

Upvotes: 2

K Richard
K Richard

Reputation: 1984

I agree with prekolna's answer that it is probably what you really need. However, to produce what you have in your question, you only need to group by the date since the dates are all the same in the month and since you have the dates displayed in the return:

SELECT
    [Date],
    SUM(Price) Price
FROM
    a_table
GROUP BY
    [Date];

The above assumes you only have one unique date entry per month and that you want the full date returned. These are admittedly bad assumptions. I would change prekolna's example slightly to ensure the date column can sort properly and to match it in the group by:

SELECT
    CAST(DATEPART(YEAR, [Date]) AS varchar(4)) + '-' + RIGHT('0' + CAST(DATEPART(MONTH, [Date]) AS varchar(2)), 2) YearMonth,
    SUM(Price) Price
FROM
    a_table
GROUP BY
    CAST(DATEPART(YEAR, [Date]) AS varchar(4)) + '-' + RIGHT('0' + CAST(DATEPART(MONTH, [Date]) AS varchar(2)), 2);

Or, you can do something else to return a full date like a MIN:

SELECT
    MIN([Date]) [Date],
    SUM(Price) Price
FROM
    a_table
GROUP BY
    CAST(DATEPART(YEAR, [Date]) AS varchar(4)),
    CAST(DATEPART(MONTH, [Date]) AS varchar(2));

Upvotes: 0

prekolna
prekolna

Reputation: 1578

You'll want to use the date functions provided in a group by statement, something like:

select sum(price)
       ,cast(datepart(month, date_column) as varchar) + '-' + cast(datepart(year, date_column) as varchar) as year_month
from your_table
group by datepart(year, date_column), datepart(month, date_column)

Make sure to put the datepart(year, date_column) first, so that you don't get a sum of all prices from every January from all years, but instead get a sum of all prices from January 2012.

The result set for this query will look a little different than your example - you'd get:

07-2012 15000
08-2012 12000
09-2012 10000
10-2012 0

If you want to get the exact result set you provided, you can change the first column of the select statement:

select sum(price)
       ,date_column
from your_table
group by datepart(year, date_column), datepart(month, date_column)

The downside to this is that if you have multiple days from the same month/year, say 8/12/2013 and 8/13/2013, it will be hard to predict which of the two days you'll get in the final result set.

Upvotes: 1

Related Questions