Posted by Alex on 09/06/05 17:23

Hi all,

I'm running into a road block, and I know I've done this before. I'm
getting fields from two tables, and I need to do a count of similar
items with it showing some extra info.

Here's my fields:
Log.LogId - Int
Log.LogDispatcherID - Int
Officer.OfficerID - Int
Officer.OfficerFirstName - Varchar
Officer.OfficerLastName - Varchar

I can get the info I need without a count with this:

select a.LogID,
a.LogDispatcherID,
b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerName
from [Log] a, Officer b
where a.LogAssigned1 = b.OfficerID

But when I try to add a count and group-by it errors out:

select Count(a.LogID) as LogCount,
a.LogDispatcherID,
b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerName
from [Log] a, Officer b
where a.LogAssigned1 = b.OfficerID
Group By a.LogID

I've done this before, but this isn't working. It's giving the error
"it is not contained in either an aggregate function or the GROUP BY
clause" for each field other then LogID.

How can I do this? I want output similar to this:

LogCount LogDispatchID OfficerName
3 34 Tom Jones
4 22 John Smith
.... Etc

Thanks for any suggestions or ideas...

Sam Alex

• Next in forum: Re: Getting count with multiple fields
• Prev in forum: Re: Massive delete in DB
• Thread view: Getting count with multiple fields

[Reply to this message]