I am performing a SQL query in PostgreSQL using a CASE
statement like this:
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
I need a fourth column which has to be the result of newcol2 * newcol3
, how can I do that?
If I put (newcol2 * newcol3) AS newcol4
I get a syntax error.
You can always use a CTE to abstract things away to a different level, if that helps - something along the lines of ...
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
A CTE is a valid approach, giving additional options.
For a simple case like this a plain subquery is simpler and slightly faster.
SELECT *, (newcol2 * newcol3) AS newcol4
FROM (
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) AS newcol3
FROM table
GROUP BY 1, 2
) AS sub
BTW: I removed newcol3
from GROUP BY
, since you are running the aggregate function count()
on it, which is slightly nonsensical.