Complicated SQL Query

[pjtien]

I want to select a query grouped by a policy number but can't seem to do it any way.

For example:
Say I have a table with 2 coloums-
Policy_Number Policy_Name
1 policy1
1 policy2
2 policy3
2 policy4
3 policy1
3 policy2
3 policy3

and I want the query to return
Policy_Number Counter
1 1
2 0
3 0

which is selecting the number of times the policy names in a policy number is contained in another policy number. In this case policy number 1 includes policy 1 and 2 which are both contained in policy number 3, so counter for policy number 1 increments. Likewise for policy numbers 2 and 3 but their policies are not contained in any other policy numbers, thus their counter remains zero.

How do I execute this query in SQL alone? No PL is allowed...

Would be great if I can get some help. Thanks!

 

[MelissaEvans]

You'd have to use it for every item, but
Select Count(*) from <table> where Policy_Number = &quot;<value>&quot;
might be useful (and tedious...)

Good luck!
~Melissa

 

[bosully]

Hi,

I hope I've understood your problem right: You want to count the number of times Policy# appears against different numbers?

Try this:

SELECT (xName), Count(xNumber)
FROM Table
WHERE xName in (Select distinct xName from Table)
GROUP BY xName;


This assumes all data in Table and xName is Policy1 etc, and xNumber is 1,2,3 etc. I tried it in Access using the data you have above and it returned:

Policy 1 2
Policy 2 2
Policy 3 2
Policy 4 1

, but that was in Access!

Give it a go if it's what you were after.

Brendan