View previous topic :: View next topic
|
Author |
Message |
noorkh
New User
Joined: 06 Mar 2006 Posts: 76 Location: Chennai
|
|
|
|
Hi,
These are my requirement.
I have one table called TVEHICLE.
There are 2 columns Policy no, Vehicle code.
I want to know the count of distinct vehicle code for the same policy no.
For eg:
Policy no Vehicle code
1234 45
1234 46
1234 47
1234 47
1236 48
1236 49
So the output should be
Policy no count
1234 2 for 45,46 ( 47 should not be considered as it is duplicate)
1235 2
Can anyone of you give me idea how to query for this?
Thanks Noor |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
here is your query
SELECT POLICY_NO,COUNT(*) FROM SESSION.POLICY
WHERE VEH_NO NOT IN(SELECT VEH_NO FROM SESSION.POLICY
GROUP BY VEH_NO HAVING COUNT(*) > 1) GROUP BY POLICY_NO; |
|
Back to top |
|
|
noorkh
New User
Joined: 06 Mar 2006 Posts: 76 Location: Chennai
|
|
|
|
Thanks |
|
Back to top |
|
|
|