Aggregate problem

  • Thread starter Thread starter MrBitsy
  • Start date Start date
M

MrBitsy

I have the following SQL

SELECT tblEmployee.EmployeeID, tblEmployee.EmployeeFirstName & ' ' &
tblEmployee.EmployeeSurname AS Fullname, Count(EmployeeID) AS Hits
FROM tblEmployee
WHERE (((tblEmployee.EmployeeID)
In (5,2164,1419,21,41,217,365,21,41,217,12,365,41,67,217)))
GROUP BY tblEmployee.EmployeeID, tblEmployee.EmployeeFirstName & ' ' &
tblEmployee.EmployeeSurname;

Why am I always getting back 1 for hits against every row? I can see
employeeID number 41 is in the list 3 times so I would expect to see 3?

What have I done wrong here?

Ray Keattch.
 
It should work. I pasted your SQL into a database and it worked perfectly.
Are you absolutely certain that the first and surnames are exactly the same
in each record. That's the only way I can make it fail.
 
Just because you are looking three times for something doesn't mean you will get
three copies of it returned. If there is only one row with the employee id of
41, then only one row will be returned.

If you have a bag of 1 black marble and 19 white marbles and look in the bag for
the black marble three times, you will still only have one black marble.
 
John, I am not sure I understand your point. Are you saying that I am
getting 1 against every row because that employees record only exists
once in the employee table, and that is what count is returning, rather
than the total number of records in the set in my example below?

Ray Keattch.
 
MrBitsy said:
John, I am not sure I understand your point. Are you saying that I am
getting 1 against every row because that employees record only exists
once in the employee table, and that is what count is returning, rather
than the total number of records in the set in my example below?
Click to expand...

You should get ONE row per employee because that is what you are grouping on.
The VALUE of your count for that employee with three rows in the source table
should be 3 however.
 
You should get ONE row per employee because that is what you are
grouping on. The VALUE of your count for that employee with three rows
in the source table should be 3 however.
Click to expand...

I don't understand this at all then!

I get back one row for each employee but every count is 1! Someone else
says the SQL I posted should work but it doesn't! This is the SQL again.

..lngScoredFilter.RowSource = "Select Count (tblEmployee.EmployeeID), " _
& "tblEmployee.EmployeeID, tblEmployee.EmployeeFirstName & ' ' &
tblEmployee.EmployeeSurname as Fullname " _
& "FROM tblEmployee " _
& "WHERE EmployeeID IN (" & strFilter & ") " _
& "Group By tblEmployee.EmployeeID, tblEmployee.EmployeeFirstName &
' ' & tblEmployee.EmployeeSurname"

strFilter contains a comma delimited list of numbers, where several
employee ID's exist more than once. If the code is right, I do not
understand why every row is showing 1 as the count!

Ray Keattch.
 
MrBitsy said:
I don't understand this at all then!

I get back one row for each employee but every count is 1! Someone else
says the SQL I posted should work but it doesn't! This is the SQL again.

.lngScoredFilter.RowSource = "Select Count (tblEmployee.EmployeeID), " _
& "tblEmployee.EmployeeID, tblEmployee.EmployeeFirstName & ' ' &
tblEmployee.EmployeeSurname as Fullname " _
& "FROM tblEmployee " _
& "WHERE EmployeeID IN (" & strFilter & ") " _
& "Group By tblEmployee.EmployeeID, tblEmployee.EmployeeFirstName &
' ' & tblEmployee.EmployeeSurname"

strFilter contains a comma delimited list of numbers, where several
employee ID's exist more than once. If the code is right, I do not
understand why every row is showing 1 as the count!
Click to expand...

Ok, perhaps I misunderstood your original question. How many times the
EmployeeID appears in the IN clause is irrelevant other than if a value
appears zero times it won't appear in the output and if it appears one or
more times it will.

The aggregate function (in this case Count()) does not care how many times
a value occurs in the IN clause. Only how many rows it finds with that
value in the tables of the FROM clause.
 
Ok, perhaps I misunderstood your original question. How many times
the EmployeeID appears in the IN clause is irrelevant other than if a
value appears zero times it won't appear in the output and if it
appears one or more times it will.

The aggregate function (in this case Count()) does not care how many
times a value occurs in the IN clause. Only how many rows it finds
with that value in the tables of the FROM clause.
Click to expand...

I am unsure how to count the returned employee Id's in that case. If an ID
appears in the 'IN' list more than once, can I count them using SQL or
should I do it from VBA?

Thanks,
Ray Keattch
 
MrBitsy said:
I am unsure how to count the returned employee Id's in that case. If an ID
appears in the 'IN' list more than once, can I count them using SQL or
should I do it from VBA?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you have a SELECT clause like this:

Select Count (EmployeeID), EmployeeID,
EmployeeFirstName & ' ' & EmployeeSurname as Fullname

the Count() function will return the number of Employee IDs & Names
per ID. If you only have one employee per ID then the Count() will
equal 1 every time.

If you want to count the total number of employees your select clause
would look like this:

SELECT Count(*) As TotalEmployees
FROM Employee
.... etc. ...

If you want the total number of all listed employee IDs/Names - you
can't (qualified - it can be done, but each record would have the
total for the whole recordset).

You could just run the query in datasheet view & move the cursor to
the last record & see the record number in the record navigator
control (bottom of datasheet). If you're using VBA you can use the
Recordset's RecordCount property (after .MoveLast) to get the total
records returned in the recordset.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFsM/4echKqOuFEgEQJjRACg40WTp6ri77Z0yaOcV5hS5i2v8UkAnj5G
BGNpUCTXu1vQGzMQSdIMxgaP
=o+V9
-----END PGP SIGNATURE-----
 
Back
Top