473,320 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Getting count with multiple fields

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

Sep 6 '05 #1
2 3942
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.LogDispatcherID),
max(b.OfficerFirstname + ' ' + b.OfficerLastname) 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

Sep 6 '05 #2
haggul
7
Try this :

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.LogDispatcherID, b.OfficerFirstname + ' ' + b.OfficerLastname

You need to specify in the group by that values that will result in the record splits in the results
Sep 6 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Bill | last post by:
I'm submitting data through a form with multiple rows on it, that goes out to a processing page, which then scrolls through the data row by row and sends it into a database table. It is...
4
by: Tim Morrison | last post by:
SQL Server 2000 - MSDE 2000 Is there a way to get the number of current users logged into a SQL 2000 Server (also MSDE)? Cant be distinct users as most users are logged into the database using the...
7
by: Schraalhans Keukenmeester | last post by:
X-Followup: comp.lang.php I have a PHP script that adds messages to a simple MySQL Database. (PHP 5.0.3, MySQL 4.1.1) One of the fields it stores is msgid. The new msgid is a count of all...
2
by: JimJim | last post by:
Wondering if someone here could help me out, I've tried a number of ways to do this, and while I have managed to get it to work, Im SURE im not doing the optimal way, and it seems to be causing...
2
by: cefrancke | last post by:
I can't seem to find a straight answer for my specific issue. Any help would be appreciated. I would like to count the various items in a table where the fields have a 'group' relationship. I...
1
by: griemer | last post by:
I have a database like this id, field1,field2,field3,field4,field5 Database contains 100 rows, some rows have no fields filled, some 1field , some 2 fields etc. How would i count the...
1
by: Phoenix_ver10 | last post by:
I have a mailing list with multiple names going to the same addresses. I need one address with all the names for that address on it. I checked out the example on microsoft's site, but A: It doesn't...
0
by: midnight_use_only | last post by:
although i don't think this is do-able using SQL, i would ask and maybe an expert can help me out. assum i have the following table: Date Code Count 2006-06-06 abc 1234...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.