Group By Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying get a count of records for a lead location where the record
number is greater than a certain number. It does great for selecting and
counting the number of records for each lead location but it bombs when I try
to give it criteria for which records to count. What am I doing wrong? It
keeps giving me an error that says I am trying to execute a query that does
not include the specified expression 'recordNum' as part of a aggregate
function. I am running Access 2003.

SELECT NewLVRMain.LeadLoc, Count(NewLVRMain.RecordNum) AS CountOfLeadLoc,
NewLVRMain.RecordNum
FROM NewLVRMain
GROUP BY NewLVRMain.LeadLoc
HAVING (((NewLVRMain.RecordNum)>=160000));

Thanks for any help.

Thanks - Jen
 
Try
SELECT NewLVRMain.LeadLoc,
Count(NewLVRMain.RecordNum) AS CountOfLeadLoc,
NewLVRMain.RecordNum
FROM NewLVRMain
WHERE NewLVRMain.RecordNum>=160000
GROUP BY NewLVRMain.LeadLoc

OR
SELECT NewLVRMain.LeadLoc,
Count(NewLVRMain.RecordNum) AS CountOfLeadLoc,
NewLVRMain.RecordNum
FROM NewLVRMain
GROUP BY NewLVRMain.LeadLoc, RecordNum
HAVING (((NewLVRMain.RecordNum)>=160000));

First version applies the WHERE clause and then does the aggregation.
Second version does the aggregation and then applies the criteria in the
HAVING clause.

Guess which is more efficient ( the first one). However, there might be
times you want to use HAVING clause such as when you want tp return rows
where Count(NewLVRMain.RecordNum) > 100
 
It almost works. (The 2nd one) The first one bombed like you thought. It
got rid of the error for the aggregation, however now it is returning every
record with a count of 1 for the CountOfLeadLoc. I think that having to
include the RecordNum in the group by part of the query is causing the error.
If I try removing it then I get that same agg. error. Is it possible to
even perform this query? I would think that I could since all I am doing is
adding criteria to it. I'm stumped.
 
My fault; I should have dropped RecordNUM from the Select Clause

SELECT NewLVRMain.LeadLoc,
Count(NewLVRMain.RecordNum) AS CountOfLeadLoc
FROM NewLVRMain
WHERE NewLVRMain.RecordNum>=160000
GROUP BY NewLVRMain.LeadLoc
 
Thanks John - worked like a charm. It's been awhile since I was in a SQL
class but it rang a bell once I saw it.
 
Back
Top