By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,890 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Multiple Counts in 1 query

P: 7
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
Share this Question
Share on Google+
9 Replies


debasisdas
Expert 5K+
P: 8,127
Kindly post the query that you are working on with your underlying table structure.
Jul 14 '08 #2

P: 7
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
Expert 100+
P: 2,367
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

P: 7
Tried it and got syntax errors. Sorry could not get screen print
Aug 1 '08 #5

amitpatel66
Expert 100+
P: 2,367
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

P: 7
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

P: 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

P: 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.  
Wondering why SUM was used instead of count. just learning SQL
Aug 10 '08 #9

P: 7
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.