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.

Jul 14 '08 #1

debasisdas

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

Jul 14 '08 #2

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%');

Jul 15 '08 #3

amitpatel66

Try This:

Jul 15 '08 #4

ncyankeefan

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

Aug 1 '08 #5

amitpatel66

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

Aug 1 '08 #6

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 !!

Aug 10 '08 #7

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.

Aug 10 '08 #8

ncyankeefan

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

Aug 10 '08 #9

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_Timestamp" 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_Timestamp" 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');

Aug 10 '08 #10