Обсуждение: 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