Group Records and Sum by Month

seanmorris
seanmorris used Ask the Experts™
on
Hi,

The following represents a table that i have which i want to create a stored procedure from.

An example of the table is as below

ID,    Date,                Value,
1,        2010/05/01,        0.25
1,        2010/04/01,        1.69
1,        2010/03/01,        8.9
1,        2010/03/02,        2.68
2,        2010/01/02,        1.5
2,        2010/01/03,        0.6
2,        2010/02/20,        3.5
etc etc etc

I have a large amount of data with many ID's and values recorded on different dates.

I would like to create a stored procedure which displays the total sum of the values per month per ID.

So for example, the output of the query would be ID, Months 1-12 and then have the total value per month grouped by ID.

ID,   M1,   M2,     M3,       M4,     M5,     .....etc etc
1,        0,      0,       11.58,    1.69,   0.25
2,        2.1,   3.5,     0,          0,        0

Any help would be much appreciated as im strugling on this one

Cheers
SM
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
You want a cross-table report or a list report.

If there are more than one year, how do you want it to show you?
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
What is "Month 1"?  Is it always the current month?

And, what do you want the headings for the months to be?  "Month 1", "Month 2", etc., is probably not good enough unfortunately.  To get custom month headings, such as "Oct 10", "Nov 10", etc., will require some type of dynamic SQL.

You can use a PIVOT table.  Or an "older-style" cross-table report.

Commented:
Try this query --------------


SELECT *
FROM (
SELECT ID1 AS Ident,
CASE MONTH(Date1)
      WHEN 1 THEN 'M1'
      WHEN 2 THEN 'M2'
      WHEN 3 THEN 'M3'
      WHEN 4 THEN 'M4'
      WHEN 5 THEN 'M5'
      WHEN 6 THEN 'M6'
      WHEN 7 THEN 'M7'
      WHEN 8 THEN 'M8'
      WHEN 9 THEN 'M9'
      WHEN 10 THEN 'M10'
      WHEN 11 THEN 'M11'
      WHEN 12 THEN 'M12'
    END as [M], Value1
FROM pivotTest) up
PIVOT (SUM(Value1) FOR M IN ([M1],[M2],[M3],[M4],[M5],[M6],[M7],[M8],[M9],[M10],[M11],[M12])) AS pvt


Regards,
Amar
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
How do you manage (or want to manage) multiple years???

Author

Commented:
The report will always run from a 12 month basis, so from January to December.

Cheers
It will be bettet to cast the date on yearly basis assuming their will be more than one year
Commented:
You can try this. If you want, you can filter by year too
select *
from
(
	select Id, Year(Date) Y, Month(Date) M, Value
	from TABLE_NAME
) SourceTable
PIVOT
(
SUM(Value)
FOR M IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS PivotTable

Open in new window

Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Looks pretty good overall.  But I would add a WHERE clause to limit the rows in the SELECT if possible.  And you don't want to use a function on the date column, because that would prevent an index lookup.  [Also, personally I prefer Jan Feb Mar, etc., :-) ]  So for the main query above, I suggest something like the code below:


DECLARE @year int
SET @year = YEAR(GETDATE()) --or however you set the year 

SELECT * 
FROM (
    SELECT ID, LEFT(DATENAME(MONTH, Date), 3) AS Mth, Value
    FROM tablename
    WHERE Date >= CAST(@Year AS char(4)) + '0101' AND Date < CAST(@Year + 1 AS char(4)) + '0101'
) AS SourceTable
PIVOT 
(
SUM(Value)
FOR Mth IN ([Jan], [Feb], [Mar], ..., [Dec])
) AS PivotTable

Open in new window

Commented:
Ok. If you prefer Month names, it's fine. But don't compare dates as strings. If you want to filter, use this:
DECLARE @year int
SET @year = YEAR(GETDATE()) --or however you set the year 

SELECT * 
FROM (
    SELECT ID, LEFT(DATENAME(MONTH, Date), 3) AS Mth, Value
    FROM tablename
    WHERE Year(Date) = @Year
) AS SourceTable
PIVOT 
(
SUM(Value)
FOR Mth IN ([Jan], [Feb], [Mar], ..., [Dec])
) AS PivotTable

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
The date *won't* be compared as a string, the string will be converted to a date.

Your method prevents an index on date from being used.  That's exactly what I was trying to avoid.  You cannot perform any function on fhe table column and still have an index be used.

Even if there is no index on date now, there could be in the future.

[A final, very minor performance gain:
A function on fhe table column must be performed for every row -- 10M rows, 10M function calls.
Function(s) on the static side of the comparison are performed only once.
]

Commented:
You are right
Steve WalesSenior Database Administrator

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial