469,356 Members | 2,033 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

Multiple Counts in 1 query

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
9 19936
debasisdas
8,127 Expert 4TB
Kindly post the query that you are working on with your underlying table structure.
Jul 14 '08 #2
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
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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
  3.  
  4.  
Jul 15 '08 #4
Tried it and got syntax errors. Sorry could not get screen print
Aug 1 '08 #5
amitpatel66
2,367 Expert 2GB
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?
Aug 1 '08 #6
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
Try This:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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
  3.  
  4.  
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
Try This:

Expand|Select|Wrap|Line Numbers
  1.  
  2. 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
  3.  
  4.  
Wondering why SUM was used instead of count. just learning SQL
Aug 10 '08 #9
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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Scott Cannon | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.