我有一个名为“DATA”的 SQL 表,如下所示:
id plan mode cost. purchaseDate
1. cd3. ANNUALLY 34. 2020-05-04
2. fg45. QUARTERLY 456. 2021-01-02
我试图返回以下 3 列:plan、planCount、totalCostPerPlan
我一直在做一个查询来返回这个,到目前为止它看起来像这样:
SELECT
plan,
COUNT(id) as planCount,
CASE
WHEN mode = 'ANNUALLY' THEN SUM(cost)
WHEN mode = 'S/ANNUALLY' THEN SUM(cost * 2)
WHEN mode = 'QUARTERLY' THEN SUM(cost * 3)
WHEN mode = 'MONTHLY' THEN SUM(cost * 12)
ELSE SUM(cost)
FROM DATA
WHERE purchaseDate >= *not important*
GROUP BY plan, mode
ORDER BY plan ASC
它有点工作,但是,我得到的数据仅部分分组如下:
plan. planCount. totalCostPerPlan
cd3. 5 *not important*
cd3. 600 *not important*
cd3. 32 *not important*
fg45. 1 *not important*
fg45. 10 *not important*
h100. 7 *not important*
(表格只是一个例子)
如何按计划进一步分组?我正在尝试获取每个计划的总值,但是,它们显示为每种模式的单独行。请协助。
嗯。. . 如果您想将因素放入 ,sum()
则case
是 的参数sum()
:
SELECT plan, COUNT(id) as planCount,
SUM(CASE WHEN mode = 'ANNUALLY' THEN cost
WHEN mode = 'S/ANNUALLY' THEN cost * 2
WHEN mode = 'QUARTERLY' THEN cost * 3
WHEN mode = 'MONTHLY' THEN cost * 12
ELSE cost
END)
FROM DATA
WHERE purchaseDate >= *not important*
GROUP BY plan
ORDER BY plan ASC
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句