Multiple Counts in 1 query

[ncyankeefan]

I am trying to get the number of specific alerts for each month of the year. I would like to get these counts in one query or subquery. There is a date field in table so I can specifically get the counts for each individual month. Any help would be appreciated.

[debasisdas]

Kindly post the query that you are working on with your underlying table structure.

[ncyankeefan]

I was wondering if this code would work in a sub query, not sure of the syntax.In this example, I am trying to get a count of two different types of alerts (ODC &ODW) from column Situation Name.. I want to get all the counts in one query so I can eventually do some neat graphs with the data.

Select
Count1=(Select Count (*) From TIVOLI."Status_ History"
Where "Situation_Name " Like 'ODC%'),
Count2=(Select Count(*) From TIVOLI."Status_ History"
Where "Situation_Name " Like 'ODW%');

[amitpatel66]

I was wondering if this code would work in a sub query, not sure of the syntax.In this example, I am trying to get a count of two different types of alerts (ODC &ODW) from column Situation Name.. I want to get all the counts in one query so I can eventually do some neat graphs with the data.

Select
Count1=(Select Count (*) From TIVOLI."Status_ History"
Where "Situation_Name " Like 'ODC%'),
Count2=(Select Count(*) From TIVOLI."Status_ History"
Where "Situation_Name " Like 'ODW%');

Try This:

[code=oracle]

SELECT SUM(CASE WHEN situation_name LIKE 'ODW%' THEN 1 ELSE 0 END) cnt_odw, SUM(CASE WHEN situation_name LIKE 'ODC%' THEN 1 ELSE 0 END) cnt_odc FROM TIVOLI."Status_ History

[/code]

[ncyankeefan]

Tried it and got syntax errors. Sorry could not get screen print

[amitpatel66]

Tried it and got syntax errors. Sorry could not get screen print

What is the error that it is displaying?
And let me know the oracle version that you are using?

[ncyankeefan]

I corrected the syntax errors. "System_History " needed double quotes at the end and "Situation_Name " need the first letter to be in caps. I need to add a few more things but you got me in the right direction. Thank yo so much !!

[ncyankeefan]

Try This:

[code=oracle]

SELECT SUM(CASE WHEN situation_name LIKE 'ODW%' THEN 1 ELSE 0 END) cnt_odw, SUM(CASE WHEN situation_name LIKE 'ODC%' THEN 1 ELSE 0 END) cnt_odc FROM TIVOLI."Status_ History

[/code]

How would I get these counts for a certain time period. (monthly weekly etc). I want to have a bar graph that shows me all the ODC/ODW alerts for the month of JAN, FEB etc I know I need to add WHERE statement for the different time periods but how to code it and get the count for each month is my problem.

[ncyankeefan]

Try This:

[code=oracle]

SELECT SUM(CASE WHEN situation_name LIKE 'ODW%' THEN 1 ELSE 0 END) cnt_odw, SUM(CASE WHEN situation_name LIKE 'ODC%' THEN 1 ELSE 0 END) cnt_odc FROM TIVOLI."Status_ History

[/code]

Wondering why SUM was used instead of count. just learning SQL

[ncyankeefan]

How would I get these counts for a certain time period. (monthly weekly etc). I want to have a bar graph that shows me all the ODC/ODW alerts for the month of JAN, FEB etc I know I need to add WHERE statement for the different time periods but how to code it and get the count for each month is my problem.
See my code below, received Oracle SQL 933 error

SELECT SUM(CASE WHEN "Situation_Name " LIKE 'ODW%' THEN 1 ELSE 0 END) cnt_jan
From TIVOLI."Status_ History"
Where "OD_Timesta mp" Between
TO_TIMESTAMP ('31-Dec-2007 00:00:01.000000 ', 'DD-Mon-YYYY HH24:MI:SS.FF') and
TO_TIMESTAMP ('01-Feb-2008 23:59:59.999999 ', 'DD-Mon-YYYY HH24:MI:SS.FF') ,
SELECT SUM(CASE WHEN "Situation_Name " LIKE 'ODW%' THEN 1 ELSE 0 END) cnt_feb
From TIVOLI."Status_ History"
Where "OD_Timesta mp" Between
TO_TIMESTAMP ('31-Jan-2008 00:00:01.000000 ', 'DD-Mon-YYYY HH24:MI:SS.FF') and
TO_TIMESTAMP ('01-Mar-2008 23:59:59.999999 ', 'DD-Mon-YYYY HH24:MI:SS.FF') ;