Percentage, sum SQL 1

[rt52]

Hi. For simplicity, the table has two fields
[location],[type].
SQL = Select loc, type, count[type] AS [number] from table
Group BY loc, type;

assume percent = count(number)/sum(count(number))

On report
GROUP
loc
Detail
type number percent
Report Footer
sum(number) sum(percent)

My problem is with the aggregate sum(percent) since I have already used sum(number) to arrive at percent.
Thanks.

 

[Crowley16]

try sum(count(number)/sum(count(number))

--------------------
Procrastinate Now!

 

[rt52]

Hi. Thanks for the response, but that was my first try. Doesn't work because of the aggregate sum. Access help says ... "You can't use the name of a control in an expression that uses an aggregate function; you must use only field names from a table, query, or SQL statement."
I've looked at help in "About computing a total for a calculated control on a form or report" but I'm not getting it.

 

[rt52]

The error message reads "Can't have aggregate function in expression (Sum((Count([number])/(Sum(Count([number])))).

 

[PHV]

And what about this ?
Select loc, type, count(type) AS [number]
,Count(type)/(SELECT Count(type) FROM table) As [percent]
from table
Group BY loc, type;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rt52]

Didn't do it.
So.. My SQL...

rptSQL = "SELECT Distinct [tbIntervention].[loc], " & _
"Count([tbIntervention].[loc]) as [Number], " & _
"Count(tbIntervention.[loc])/" & _
"(Select Count([tbIntervention].[loc]) " & _
"FROM tbIntervention AS [percent]) " & _
"FROM tbIntervention " & _
"GROUP BY [tbIntervention].[loc]"

when I use this as a REPORT sql, and place percent as the control source for the field, I get an
"Enter Parameter Value" for percent, and =Sum([percent]) therefore doesn't work either.
(Placing "number" as the control source for the number field
works as does "=Sum([number]"in the footer for number. Placing "=(number)/Sum(number)" works for percentage field, but I still don't get a total for percentage).

 

[PHV]

Replace this:
rptSQL = "SELECT Distinct [tbIntervention].[loc], " & _
"Count([tbIntervention].[loc]) as [Number], " & _
"Count(tbIntervention.[loc])/" & _
"(Select Count([tbIntervention].[loc]) " & _
"FROM tbIntervention AS [percent]) " & _
"FROM tbIntervention " & _
"GROUP BY [tbIntervention].[loc]"
By this:
rptSQL = "SELECT [tbIntervention].[loc], " & _
"Count([tbIntervention].[loc]) as [Number], " & _
"Count(tbIntervention.[loc])/" & _
"(Select Count([tbIntervention].[loc]) " & _
"FROM tbIntervention) AS [percent] " & _
"FROM tbIntervention " & _
"GROUP BY [tbIntervention].[loc]"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rt52]

Hi. I see I had a parenthesis off ... thank you very much!
RT

 

[rt52]

Another question please. The query I use ...

Select Case rpttype
Case 1
rptSQL = "SELECT [tbIntervention].[loc], " & _
"Count([tbIntervention].[loc]) AS [Number], " & _
"Count([tbIntervention].[loc])/" & _
"(Select Count([tbIntervention].[loc]) " & _
"FROM tbIntervention) AS [percent] " & _
"FROM tbIntervention "

rptGrp = "GROUP BY [tbIntervention].[loc]"
stDocName = "rptLocSummary"
Case 2
Case ...
End Case

rptSQL = rptSQL & " WHERE " & _
dateSQL & " AND " & cboSQL & " " & _
rptGrp & _
";"
allows me to grab several criteria. The initial select statement uses date criteria and is a subset of the whole database. The nested select grabs the whole database. Therefore my percentages are percentages of the whole database and not just the recordset from the first select statement. How would I limit the percentages just to the subset of the first Select statement?
Thanks again.

 

[PHV]

rptSQL = "SELECT [tbIntervention].[loc], " & _
"Count([tbIntervention].[loc]) AS [Number], " & _
"Count([tbIntervention].[loc])/" & _
"(Select Count([tbIntervention].[loc]) " & _
"FROM tbIntervention WHERE " & _
dateSQL & " AND " & cboSQL & ") AS [percent] " & _
"FROM tbIntervention "


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rt52]

Thanks. You've been very patient.
Now I am getting an error
"Multi-level Group By clause is not allowed in a subquery."

What does this mean?

The report only has one group = loc.