467,198 Members | 1,230 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

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

Combine queries for single output

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
  • viewed: 3901
Share:
4 Replies
8TB
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
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
8TB
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
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.

Similar topics

reply views Thread by MostlyH2O | last post: by
4 posts views Thread by Jason Gyetko | last post: by
1 post views Thread by MackTheKnife | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.