在if子句中使用平均聚合函数
在if子句中使用平均聚合函数
提问于 2013-06-25 05:48:16
我正在编写一个查询，其中需要根据avg()聚合函数的条件从select stmt生成结果，
但在mysql中，它将错误作为错误代码: 1111，组函数的无效使用。
select
if( avg(FLD_TKTS) > 1 and avg(FLD_TKTS) <= 2 , '1-2' , 0) as fld_avg_1_2,
if( avg(FLD_TKTS) > 3 and avg(FLD_TKTS) <= 4 , '3-4' , 0) as fld_avg_3_4
from tbl
group by region;还有其他方法可以做到这一点吗？
回答 3
Stack Overflow用户
发布于 2013-06-25 05:52:00
查询：
select
CASE WHEN avg(FLD_TKTS) > 1 and avg(FLD_TKTS) <= 2 THEN '1-2'
ELSE '0' END as fld_avg_1_2,
CASE WHEN avg(FLD_TKTS) > 3 and avg(FLD_TKTS) <= 4 THEN '3-4'
ELSE '0' END as fld_avg_3_4
from tbl
group by regionStack Overflow用户
发布于 2013-06-25 05:53:00
select
region,
if(fld_avg > 1 and fld_avg <= 2, '1-2', 0) as fld_avg_1_2,
if(fld_avg > 3 and fld_avg <= 4, '3-4', 0) as fld_avg_3_4
from
(
select
region,
avg(FLD_TKTS) fld_avg
from
tbl
group by
region
) a;Stack Overflow用户
发布于 2013-06-25 05:53:22
我认为子查询是一个很好的解决方案：
SELECT
IF( avg_fld_tkts > 1 and avg_fld_tkts <= 2 , '1-2' , 0) as fld_avg_1_2,
IF( avg_fld_tkts > 3 and avg_fld_tkts <= 4 , '3-4' , 0) as fld_avg_3_4
(
SELECT avg(FLD_TKTS) AS avg_fld_tkts
from tbl
group by region
) t1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/17290011
复制相关文章
相似问题
腾讯云开发者
