Get an Average of a Count

[judgeh59]

I'm trying to get an Average of a count in an SQL statement. Here is the statement.

SELECT SFC, COUNT(SFC) as NumberSfc
FROM plr_data
WHERE ASSY="50060D" AND EVENT_DATE>=#5/23/2011# AND NC_CODE = "FF" AND PROCESS = "1-475-MV1"
GROUP BY SFC;


this produces a table output like:

SFC NumberSFC
S01234 20
S05463 1
S05463 17
...

I want to Average the NumberSFC column. I'm sure it's easy but my brain is pudding right now (for other reasons)....Thanks

Ernest


Be Alert, America needs more lerts

 

[lameid]

If your query is named Myquery...

Select Avg(NumberSfc)
From Myquery

 

[judgeh59]

lameid - thanks, that worked - I was hoping to do that inside the same SQL statement. thanks again

Ernest

Be Alert, America needs more lerts

 

[PHV]

do that inside the same SQL statement

SELECT AVG(G.NumberSfc) AS AvgNumberSfc
FROM (SELECT SFC, COUNT(*) AS NumberSfc FROM plr_data
WHERE ASSY='50060D' AND EVENT_DATE>=#2011-05-23# AND NC_CODE='FF' AND PROCESS='1-475-MV1'
GROUP BY SFC) G

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[judgeh59]

so this looks like a nested select statement....interesting...I get an error when I run this "syntax error in FROM clause"...also I believe there should be a ";" after the last "G"...that isn't causing the error....any ideas....great help though, PHV.

Ernest

Be Alert, America needs more lerts

 

[PHV]

This syntax is legal since ac2k.

 

[judgeh59]

I am running AC2007...I'm searching on subqueries to see if I can see anything...It looks good, but when I run it I get that error...I'll keep hunting...again, great job, thanks....

Be Alert, America needs more lerts

 

[BigRed1212]

If you want to do it in one SQL statement and LameID's solution works for you then break it down the brute force way:


SELECT SFC, COUNT(SFC) as NumberSfc
FROM plr_data
WHERE ASSY="50060D" AND EVENT_DATE>=#5/23/2011# AND NC_CODE = "FF" AND PROCESS = "1-475-MV1"
GROUP BY SFC
union
select "Average", avg(NumberSfc)
from
(
SELECT SFC, COUNT(SFC) as NumberSfc
FROM plr_data
WHERE ASSY="50060D" AND EVENT_DATE>=#5/23/2011# AND NC_CODE = "FF" AND PROCESS = "1-475-MV1"
GROUP BY SFC
)

 

[judgeh59]

okay, in my infinite stupidity I use access 2007 for just about everything, BUT this DB, which is not mine, is an AC97 DB...how does that change the syntax....sorry about that folks....

Be Alert, America needs more lerts

 

[randy700]

I don't see anything in this thread that won't work in Access 97.
What new problems have you encountered?


Randy

 

[judgeh59]

If I just plug this into a new query and run it, I still get "Syntax error in FROM clause". When I hit OK it highlights the "SELECT". I have tried moving the parathesis around and adding AS before the G and adding ";" at the end, I have changed the (*) to (SFC) like the original, but to no avail...I'm not having any luck...

Be Alert, America needs more lerts

 

[lameid]

It's been a long while since I used Access 97 and I have never been much of a fan of using inline subqueries outside of IN statements for criteria but my hunch is that there is one word for compatibility missing....

SELECT AVG(G.NumberSfc) AS AvgNumberSfc
FROM (
      SELECT SFC, COUNT(*) AS NumberSfc 
      FROM plr_data
      WHERE ASSY='50060D' 
       AND EVENT_DATE>=#2011-05-23# 
       AND NC_CODE='FF' 
       AND PROCESS='1-475-MV1'
      GROUP BY SFC
      ) [b][red]AS[/red][/b] G;

Then again, Access 97 just might not like it.

 

[judgeh59]

I did try the AS G earlier but it didn't work...I tried your statement and I get the same error. You may be right and AC97 just doesn't like it...it does work creating a second statement and calling the saved SQL statement but that's a pain...anyway...I'm still open to any other ideas....thanks for the help....

Ernest

Be Alert, America needs more lerts

 

[BigRed1212]

Did you try my suggestion? I'm pretty sure it will run.

 

[judgeh59]

bigred - I'm sorry I didn't reply...I get the same error "Syntax error in FROM clause"...I'm starting to think AC97 really doesn't like subqueries....thanks for the try though...and again, sorry for not replying...

Be Alert, America needs more lerts

 

[PHV]

You knew that since 25 May 11 10:07 ...

 

[judgeh59]

well, I didn't "know" it...but I surely suspected it....

Be Alert, America needs more lerts