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

Combine queries for single output

P: 7
I want my output in two columns like this:

C_INCIDENT_TYPE \ TOTAL COUNT


How do I combine my two queries to get result.

First Query

SELECT C_INCIDENT_TYPE, COUNT (*)
FROM TABLE_AUDIT
WHERE SUBSTR(I_USER,7,2) IN ('AD','WF','F5')
AND SUBSTR(I_IMAGE_COPY,12,8) > '00000000'
AND SUBSTR(I_IMAGE_COPY,12,8) < '99999999'
AND D_H_STAMP BETWEEN '2007-06-01-00.37.30.124670' AND
'2007-06-30-00.37.30.124670'
GROUP BY C_INCIDENT_TYPE
ORDER BY 2
WITH UR


Second Query

SELECT C_INCIDENT_TYPE, COUNT (*)
FROM TABLE_AUDIT
WHERE SUBSTR(I_USER,7,2) NOT IN 'AD','WF','F5')
AND SUBSTR(I_IMAGE_COPY,12,8) > '00000000'
AND SUBSTR(I_IMAGE_COPY,12,8) < '99999999'
AND D_H_STAMP BETWEEN '2007-06-01-00.37.30.124670' AND
'2007-06-30-00.37.30.124670'
GROUP BY C_INCIDENT_TYPE
ORDER BY 2
WITH UR

The only statement difference between the two queries is: WHERE SUBSTR(I_USER,7,2) NOT IN 'AD','WF','F5') (The other query says 'IN')

That is my question... Thank You!
Aug 21 '07 #1
Share this Question
Share on Google+
4 Replies


10K+
P: 13,264
I want my output in two columns like this:

C_INCIDENT_TYPE \ TOTAL COUNT


How do I combine my two queries to get result.

First Query

SELECT C_INCIDENT_TYPE, COUNT (*)
FROM TABLE_AUDIT
WHERE SUBSTR(I_USER,7,2) IN ('AD','WF','F5')
AND SUBSTR(I_IMAGE_COPY,12,8) > '00000000'
AND SUBSTR(I_IMAGE_COPY,12,8) < '99999999'
AND D_H_STAMP BETWEEN '2007-06-01-00.37.30.124670' AND
'2007-06-30-00.37.30.124670'
GROUP BY C_INCIDENT_TYPE
ORDER BY 2
WITH UR


Second Query

SELECT C_INCIDENT_TYPE, COUNT (*)
FROM TABLE_AUDIT
WHERE SUBSTR(I_USER,7,2) NOT IN 'AD','WF','F5')
AND SUBSTR(I_IMAGE_COPY,12,8) > '00000000'
AND SUBSTR(I_IMAGE_COPY,12,8) < '99999999'
AND D_H_STAMP BETWEEN '2007-06-01-00.37.30.124670' AND
'2007-06-30-00.37.30.124670'
GROUP BY C_INCIDENT_TYPE
ORDER BY 2
WITH UR

The only statement difference between the two queries is: WHERE SUBSTR(I_USER,7,2) NOT IN 'AD','WF','F5') (The other query says 'IN')

That is my question... Thank You!
How do you want to combine the queries? The two conditions contradict so you can't get data that satisfies both conitions.
Aug 22 '07 #2

P: 7
How do you want to combine the queries? The two conditions contradict so you can't get data that satisfies both conitions.
I understand that the two conditions contradict each other. My goal is to get the output so it looks like this.

Therefore output will have three columns

C_INCIDENT_TYPE | COUNT (From first Query | COUNT (from Second Query |
| | |

Would I use a join to run the query? Just asking?

Thank You!
Aug 22 '07 #3

10K+
P: 13,264
I understand that the two conditions contradict each other. My goal is to get the output so it looks like this.

Therefore output will have three columns

C_INCIDENT_TYPE | COUNT (From first Query | COUNT (from Second Query |
| | |

Would I use a join to run the query? Just asking?

Thank You!
I get what you're talking about now. I don't think joining the queries is the proper way of doing it. I'd suggest doing a little bit of arithmetic with it. If you have n records and your first query returns a records, then shouldn't your second query return n - a records?
Aug 22 '07 #4

P: 7
Thanks, I will look into that more...
Aug 22 '07 #5

Post your reply

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