Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

help with grouping

Status
Not open for further replies.

sedgely

Technical User
Feb 21, 2002
406
GB
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
 
I assume you want:
Code:
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]
 
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top