使用聚合函数选择case

hfyxw5xn  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(317)

我有三个表:indi(个人),consump(消费)和manu(制造商)
我想分组的年龄类别的个人(我有实际年龄)和显示的总消费量由制造商。
比如说:

Age Category | Manufacturer | Total Volume Consumed 
15 - 24      | Coca Cola    | 10ML
             | Pepsi        | 25 ML

下面是这个示例(如何在mysql中按年龄段对用户进行分组),我创建了如下年龄段:

SELECT
CASE
WHEN age >=15 AND age <=24 THEN '15-24'
WHEN age >=25 AND age <=34 THEN '25-34'
WHEN age >=35 AND age <=44 THEN '35-44'
WHEN age >=45 AND age <=54 THEN '45-54'
WHEN age >=55 AND age <=66 THEN '54-64'
WHEN age >=65 THEN '65+'
END AS ageband

链接表的键是 id 对于 indi 以及 consump 表和 code 链接 consump 以及 manu table。现在,由于一个制造商生产多种饮料,因此一个制造商有多个代码。
所以 where 条款将包括以下内容: indi.id = consump.id AND consump.code = manu.code 因此,我的思考过程如下(我确实意识到我需要包含case语句,但我不知道我把它放在哪里了——作为子查询放在什么地方?)

SELECT age, manufacturer, SUM(volume) as 'Total Volume Consumed'
FROM indi, consump, manu
WHERE indi.id = consump.id AND consump.code = manu.code
GROUP BY ageband
kx1ctssn

kx1ctssn1#

只需添加案例和适当的分组即可

SELECT CASE 
        WHEN age >=15 AND age <=24 THEN '15-24'
        WHEN age >=25 AND age <=34 THEN '25-34'
        WHEN age >=35 AND age <=44 THEN '35-44'
        WHEN age >=45 AND age <=54 THEN '45-54'
        WHEN age >=55 AND age <=66 THEN '54-64'
        WHEN age >=65 THEN '65+'70
        END  as ageband
        , manufacturer
        , SUM(volume) as 'Total Volume Consumed'
    FROM indi
    INNER JOIN consump ON indi.id = consump.id
    INNER JOIN manu ON consump.code = manu.code
    GROUP BY ageband, manufacturer

相关问题