Getting count with multiple fields

[Alex]

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.LogDispatch erID - Int
Officer.Officer ID - Int
Officer.Officer FirstName - Varchar
Officer.Officer LastName - Varchar

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

select a.LogID,
a.LogDispatcher ID,
b.OfficerFirstn ame + ' ' + b.OfficerLastna me 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.LogDispatcher ID,
b.OfficerFirstn ame + ' ' + b.OfficerLastna me 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

[Simon Hayes]

Assuming that there is only one possible officer and dispatcher for
each LogID, then you could do this:

select
count(a.LogID) as LogCount,
max(a.LogDispat cherID),
max(b.OfficerFi rstname + ' ' + b.OfficerLastna me) as
OfficerName
from
dbo.[Log] a
join dbo.Officer b
on a.LogAssigned1 = b.OfficerID
Group By
a.LogID

However, that may not be what you want, depending on what your data
looks like, if there can be several officers or dispatchers for each
LogID etc. So if this isn't helpful, or gives you the wrong answer, I
suggest you post CREATE TABLE and INSERT statements to set up a simple
test case - that way others can copy and paste into QA to test it.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

[haggul]

Try this :

select Count(a.LogID) as LogCount,
a.LogDispatcher ID,
b.OfficerFirstn ame + ' ' + b.OfficerLastna me as OfficerName
from [Log] a, Officer b
where a.LogAssigned1 = b.OfficerID
Group By a.LogDispatcher ID, b.OfficerFirstn ame + ' ' + b.OfficerLastna me

You need to specify in the group by that values that will result in the record splits in the results