Multiple count and where statement with parameter

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

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple count and where statement with parameter

Author

Topic

nonsec
Starting Member

26 Posts

Posted - 2009-04-14 : 12:45:16

Hello Everyone,

I don`t know if it is right place to ask I need little bit help about a query. I want to get the results between @startdate and @enddate parameter but it counts all of them. I could not figure out how to make it work.
Any help greatly appreciated.
Thank you.

Here is my query

SELECT     DATEADD(day, DATEDIFF(day, 0, TimeResolutionStateLastModified), 0) AS Date,
(select count(severity) from alertview where severity = '2') AS Critical,
(select count(severity) from alertview where severity = '1') AS Warning,
(select count(severity) from alertview where severity = '0') AS Informational
From Alertview
WHERE     (Severity = 2) AND (TimeResolutionStateLastModified BETWEEN @startdate AND @enddate)
GROUP BY DATEADD(day, DATEDIFF(day, 0, TimeResolutionStateLastModified), 0)
ORDER BY Date

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-14 : 12:53:28

Try this:


SELECT DATEADD(day, DATEDIFF(day, 0, TimeResolutionStateLastModified), 0) AS Date
       ,count(case when severity = '2' then 1 else 0 end) AS Critical
       ,count(case when severity = '1' then 1 else 0 end) AS Warning
       ,count(case when severity = '0' then 1 else 0 end) AS Informational
From   Alertview
WHERE  TimeResolutionStateLastModified BETWEEN @startdate AND @enddate
GROUP BY DATEADD(day, DATEDIFF(day, 0, TimeResolutionStateLastModified), 0)
ORDER BY 1

Be One with the Optimizer
TG

nonsec
Starting Member

26 Posts

Posted - 2009-04-15 : 14:07:53

Thank you for your answer but I am trying to but it returns inaccurate data.

Date Critical Warning Informational
2009-04-09 00:00:00.000 563 563 563
2009-04-13 00:00:00.000 235 235 235
2009-04-14 00:00:00.000 230 230 230
2009-04-15 00:00:00.000 2 2 2

accurate one should be like this;
Date Critical Warning Informational
2009-04-09 00:00:00.000 563 20 0
2009-04-13 00:00:00.000 235 145 0
2009-04-14 00:00:00.000 230 160 1
2009-04-15 00:00:00.000 2 2 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-15 : 14:13:59

can you show query used?

Kokkula
Starting Member

41 Posts

Posted - 2009-04-15 : 14:46:02

Hello,

Try this

SELECT DATEADD(day, DATEDIFF(day, 0, TimeResolutionStateLastModified), 0) AS Date,
SUM(CASE WHEN severity = '2' THEN 1 ELSE 0 END) AS Critical,
SUM(CASE WHEN severity = '1' THEN 1 ELSE 0 END) AS Warning,
SUM(CASE WHEN severity = '0' THEN 1 ELSE 0 END) AS Informational
From Alertview
WHERE TimeResolutionStateLastModified BETWEEN @startdate AND @enddate
GROUP BY DATEADD(day, DATEDIFF(day, 0, TimeResolutionStateLastModified), 0)
ORDER BY DATEADD(day, DATEDIFF(day, 0, TimeResolutionStateLastModified), 0)

Hope helpful...

Thanks,
Pavan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-15 : 14:53:23

Ah - yes. I meant to use SUM rather than COUNT. Thanks Kokkula.

Be One with the Optimizer
TG

nonsec
Starting Member

26 Posts

Posted - 2009-04-15 : 17:03:13

Kokkula, TG thank you so much really appreciate it. It worked. : )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 10:59:11

and if you want to use count use it like below


SELECT DATEADD(day, DATEDIFF(day, 0, TimeResolutionStateLastModified), 0) AS Date
       ,count(case when severity = '2' then 1 else NULL end) AS Critical
       ,count(case when severity = '1' then 1 else NULL end) AS Warning
       ,count(case when severity = '0' then 1 else NULL end) AS Informational
From   Alertview
WHERE  TimeResolutionStateLastModified BETWEEN @startdate AND @enddate
GROUP BY DATEADD(day, DATEDIFF(day, 0, TimeResolutionStateLastModified), 0)
ORDER BY 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-16 : 11:03:00

quote:

Originally posted by visakh16

and if you want to use count use it like below


SELECT DATEADD(day, DATEDIFF(day, 0, TimeResolutionStateLastModified), 0) AS Date
       ,count(case when severity = '2' then 1 else NULL end) AS Critical
       ,count(case when severity = '1' then 1 else NULL end) AS Warning
       ,count(case when severity = '0' then 1 else NULL end) AS Informational
From   Alertview
WHERE  TimeResolutionStateLastModified BETWEEN @startdate AND @enddate
GROUP BY DATEADD(day, DATEDIFF(day, 0, TimeResolutionStateLastModified), 0)
ORDER BY 1

But I prefer using SUM(case when...) in this case as count with null will lead to this

Warning: Null value is eliminated by an aggregate or other SET operation.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources