将在CASE语句中计算出的两列相乘

Question

我正在使用以下CASE语句在PostgreSQL中执行SQL查询：

SELECT
    CASE column1
        WHEN something THEN 10
        ELSE 20
        END AS newcol1
    CASE column12
        WHEN something THEN 30
        ELSE 40
        END AS newcol2
COUNT(column3) newcol3
FROM table
GROUP BY newcol1,newcol2,newcol3

我需要第四列，该列必须是的结果newcol2 * newcol3，我该怎么做？

如果我把(newcol2 * newcol3) AS newcol4我语法错误。

Answer 1

如果有帮助，您总是可以使用CTE将事物抽象到不同的层次-类似于…

With CTE as
(
 SELECT
  CASE column1
    WHEN something THEN 10
    ELSE 20
    END AS newcol1,
  CASE column12
    WHEN something THEN 30
    ELSE 40
    END AS newcol2,
  column3,
 FROM table
)
SELECT
  newcol1, newcol2,
  count(column3) as newcol3,
 (newcol2 * newcol3) AS newcol4
FROM CTE 
GROUP BY newcol1,newcol2,newcol3