Sql Query Help for Newb 1

[drweb]

All,

I am a sql newb and in a bind and all out of ideas at the moment but I was thrown into figuring a query out to report back what my manager wants to see.
Basically I have a table similar to the one below that I need to write a query against that will give me this report style format once ran. This is going to be run on a schedule in one of our systems that will email the results daily to my manager.
I am ok with simple select and join statements but do not know where to start with manipulating the queried data inside the query.

Similar table:
hostname severity_guid timestamp
HOLSUSAS006 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 3:27
HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 5:27
HOLSUSAS004 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 9:45
HOLSUSAS006 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 5:30
HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 11:13
HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 0:46
NASADEV01 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 23:29
USPVUSFS0001 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 0:45
SSISUSTST001 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 3:27
HOLSUSFS001 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 5:27
SSISUSBEX002 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 9:45
NASADEV01 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 5:30
NASADEV01 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 11:13
SSISUSBEX002 c4cf8a23-a106-4617-bab0-94da3ca74ef1 7/11/2010 0:46
HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 23:29
NASADEV01 cb2f2b90-2da4-4075-bcaa-dd5d2cefbfd5 7/11/2010 0:45
HOLSUSAS004 0168a833-1732-411e-8205-c2f6cd91737d 7/11/2010 8:15


In this table the severity guid actually means the following:
'0168A833-1732-411E-8205-C2F6CD91737D' = 'Critical'
'CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' = 'Major'
'C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' = 'Warning'


My manager basically wants to see the number of particular alerts for a host in past 24 hours:

Server Name Critical Major Warning
HOLSUSAS006 0 2 0
HOLSUSAS004 5 1 0
NASADEV01 1 2 0
USPVUSFS0001 0 0 1
SSISUSTST001 0 0 1
SSISUSBEX002 0 0 2



Any direction or advice will be greatly appreciated.

Thanks,
Clay

 

[IanWaterman]

Use a case statement and summarise data

select hostname,
sum(case when severity_guid = '0168A833-1732-411E-8205-C2F6CD91737D' then 1 else 0 end) Critical,
sum(case when severity_guid = 'CB2F2B90-2DA4-4075-BCAA-DD5D2CEFBFD5' then 1 else 0 end) Major,
sum(case when severity_guid = 'C4CF8A23-A106-4617-BAB0-94DA3CA74EF1' then 1 else 0 end) Warning
from yourtable
Group by hostname

Ian

 

[drweb]

Ian,

Thanks for the quick reply.

This will help me greatly and get me on my way to what I ned to see.


Clay