Re: aggregate functions in "SELECT"

On 12/03/2013 10:44 PM, Gerald Cheves wrote:
> Dear Colleagues,
>
> How can I use the COUNT variable and the COL_YES variable to calculate
> a percentage COL_YES/COUNT*100?
>
> This operation isn't allowed in the "SELECT" statement.

You'll need to use a superquery. See below.

> Select g.STATE,
> g.COMPANY,
> g.MODEL,
> count(g.MODEL) as COUNT,
> coalesce(sum(case when COLORS = 'Yes' then 1 else 0 end),0) as
> COL_YES
> from gentech_12_13 as g
> where MODEL = '0387' and COMPANY = 'ACME'
> group by g.STATE, g.COMPANY, g.MODEL
> ;
>

SELECT state, company, model, count, col_yes, 100.0*col_yes/count
FROM (
Select g.STATE,
g.COMPANY,
g.MODEL,
count(g.MODEL) as COUNT,
coalesce(sum(case when COLORS = 'Yes' then 1 else 0 end),0)
as COL_YES
from gentech_12_13 as g
where MODEL = '0387' and COMPANY = 'ACME'
group by g.STATE, g.COMPANY, g.MODEL
) q;

--
Vik