Thread: aggregate functions in "SELECT"

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.

Thanks


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
;

--
siamo arrivati sani e salvi

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

Thanks so much, Vik.

- Gerald

On 12/3/2013 5:08 PM, Vik Fearing wrote:
> 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;
>


--
siamo arrivati sani e salvi