help with grouping

[sedgely]

i have a table with the following fields (among others)

id
priority
name

i need to count the number of records for each name grouped by priority, my problem is that where there are several records with the same id number for a particular name/priority i only want the result to return 1.
for example:

id priority name
001 A fred bloggs
002 A fred bloggs
002 A fred bloggs
003 A john smith
003 A john smith
004 B john smith

would return:

Count priority name
2 A fred bloggs
1 A john smith
1 B john smith

i'm sure this should be fairly straightforward but i just can't get my head around it. Any help appreciated

Cheers, Craig
Si fractum non sit, noli id reficere

 

[ca8msm]

I assume you want:

select count(*) as amount, priority, name
from mytable
group by priority, name

although your expected results do not match what you've given as example data. For example, fred bloggs has two entries with a priority of A and an id of 002, and so does john smith but for id 003 yet they both have different entries in the resultset.



-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[bborissov]

SELECT priority, name, COUNT(*) AS Ammount
FROM (SELECT DISTINCT id, priority, name
             FROM YourTable) Tbl1

Not tested.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[k01]

SELECT
COUNT(Distinct id) as COUNT
,priority
,name
FROM
table
GROUP BY
priority
,name


koichi

http://aboutabi.blogspot.com/