summing values in col2 based on value in col1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

summing values in col2 based on value in col1

Author

Topic

enzo_p
Starting Member

7 Posts

Posted - 2008-09-04 : 12:03:58

Hi,

I have a query that returns 3 columns:
DATE, SEVERITY and COUNT

In SEVERITY I get values such as "1" and "1 - Critical" returned, which are, in effect, the same thing...

e.g..

2008/09/01 1 10
2008/09/01 1 - Critical 20

...what I need to do is create a table in the report which only has one of these SEVERITY values (i.e. either "1" or "1 - Critical") but which has the sum of the COUNT value where either of these appear for a given date.

e.g..
date severity count
2008/09/01 1 30

is this summing best done in the SQL dataset or as part of the report itself?

all help much appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 12:49:06

[code]SELECT DATE,
LEFT(SEVERITY,CASE WHEN CHARINDEX('-',SEVERITY)> 0 THEN CHARINDEX('-',SEVERITY)-2 ELSE LEN(SEVERITY) END),
SUM(COUNT)
FROM table
GROUP BY DATE,
LEFT(SEVERITY,CASE WHEN CHARINDEX('-',SEVERITY)> 0 THEN CHARINDEX('-',SEVERITY)-2 ELSE LEN(SEVERITY) END) [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources