Multiplying two columns which have been calculated on a CASE statement

Question

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.

I think you forgot commas in between your columns. Is that a typo? — didierc, Mar 15 '13 at 16:45
Ups, i did it here, but not on my original query, the syntax error came when i put the (newcol2 * newcol3) AS newcol4 line. — CarlosJavier, Mar 15 '13 at 16:48

score 4 · Accepted Answer · answered Mar 15 '13 at 16:56

4

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

answered Mar 15 '13 at 16:56

AjV Jsy

5,799
4
34
30

There will be syntax error here, too. You can't `GROUP BY newcol3` (the column generated at the `SELECT` list.) – ypercubeᵀᴹ Mar 15 '13 at 17:02
Yeah, probably :) that's why I said "something along the lines of" - just to give him the general idea. It wasn't tested or anything. – AjV Jsy Mar 15 '13 at 17:08

Erwin Brandstetter · Answer 2 · 2013-03-16T01:22:32.383

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.

Multiplying two columns which have been calculated on a CASE statement

2 Answers2

Linked