| View previous topic :: View next topic |
| Author |
Message |
spalanis
Beginner
Joined: 19 Jul 2006
Posts: 32
Topics: 15
|
 Posted: Wed Aug 09, 2006 12:48 pm Post subject: How to do multiple counts and compare in a single query?. |
 |
|
Hi all,
This is my first post in MVSFORUMS and I am the silent reader of this forum for the past one month. This forum is great and informative!!.
Okay...My question is..let us assume that we have a table called policy_detail
This table has 3 fields....they are policy_no, policy_seq_no & trans_type.
The data in the table may be:
| Code: |
Policy_no Policy_seq_no Trans_type
12345 0 NBIS
12345 0 PCNM
12345 0 AUDF
12345 0 AUDR
12345 0 AUDV |
In the above table, policy_no and policy_seq_no is the key. What I need to do is :
Count 1 : I need to find the count of AUDF+AUDR
Count 2 : I need to find the count of AUDV
If count1 = count2, then this query should return "True" or "Y"
Else this query should return "False" or "N".
I know I can handle this condition using two or more queries. But Is there a way I can get the result in a single query.
Thanks,
srini |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Aug 09, 2006 1:19 pm Post subject: |
|
|
spalanis,
try this
| Code: |
SELECT CASE WHEN SUM(CASE WHEN TRANS_TYPE = 'AUDF'
OR TRANS_TYPE = 'AUDR'
THEN INT(1) ELSE INT(0) END) =
SUM(CASE WHEN TRANS_TYPE = 'AUDV'
THEN INT(1) ELSE INT(0) END)
THEN CHAR('TRUE')
ELSE CHAR('FALSE') END
FROM TABLE ;
|
btw there is already a similar question which discussed along with detailed explanation
http://www.mvsforums.com/helpboards/viewtopic.php?t=6589
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
spalanis
Beginner
Joined: 19 Jul 2006
Posts: 32
Topics: 15
|
| Posted: Wed Aug 09, 2006 3:35 pm Post subject: |
|
|
Already so many of them said "you are great" I simply do not want to repeaty that Again 
Anyway...I like your senthamizh name "Kolusu" |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
