I am trying to get a percentage "PER" eg: (CNT / TOTAL) * 100
As below, but this is not working as I get the error Non aggregated column in expression.
Is there a way to do this  as I cannot work it out with my SQL?

Thank you

SELECT COUNT(*) AS TOTAL INTO MEMORY\TEMP FROM DATA;

SELECT
Field1,
Field2,
COUNT(*) AS CNT,
(TOTAL/COUNT(*)) AS PER
FROM DATA, "MEMORY\TEMP"  
GROUP BY Field1

"Dave Bailey" <david@itfx.com.au> wrote in message
news:5AE4FD03-9E3E-4A05-95F1-C4BD58F09B88@news.elevatesoft.com...
>I am trying to get a percentage "PER" eg: (CNT / TOTAL) * 100
> As below, but this is not working as I get the error Non aggregated column
> in expression.
> Is there a way to do this  as I cannot work it out with my SQL?
>

If Field1 is unique, create the CNT values in a second memory table, then
join the whole mess.

SELECT
Field1,
COUNT(*) AS CNT
into memory\temp2
FROM DATA
GROUP BY Field1;

SELECT
Field1,
Field2,
CNT, TOTAL,
(CNT / TOTAL) * 100.0 AS PER
FROM DATA
join  "MEMORY\TEMP"
join memory\temp2 on memory\temp2.field1 = data.field1

To optimize you can 1) create TOTAL using memory\temp, so you don't have to
pass the data twice and 2) create an index on memory\temp2.