sql nested queries - case

Question

I created a new column to my dataset, here is my code :

SELECT SUM("nb"), app_name, user_id, api, CASE
WHEN api='v1.pt_ob' THEN '0.7'
WHEN api='v1.place_ur' THEN '1'
WHEN api='V2' THEN '0.4'
ELSE 'autre'
END
FROM stats WHERE app_name='CMT' or app_name='CBN' GROUP BY app_name, api, user_id

And now I am trying to add a column that is the result of the multiplication between 2 other columns (sum and case). So I tried to do this :

SELECT app_name, user_id, api, sum*case 
from (select SUM("nb")as sum, app_name, user_id, api, CASE
WHEN api='v1.pt_objects' THEN '0.7'
WHEN api='v1.place_uri' THEN '1'
ELSE 'autre'
END
FROM "stat_compiled"."requests_calls_y2022m02" WHERE app_name='CMI_transilien' or app_name='CMI - APM' or app_name='Media_SNCF.com' or app_name='Medias_TER' or app_name='CMI PIV- sncf.com' or app_name='CMI PIV - TER' GROUP BY app_name, api, user_id) as stat2

And I had the error : "ERROR: syntax error at or near "from"" I tried severals things but I can't find the solution. Can somebody help me please ?

Answer 1

Do not use results like 'autre' or 'other', or 'not known' or 'not applicable'. They only cause trouble. In a relational database, the NULL value is there exactly for that. It tells whoever wants to read that column to not bother to read, that there is no information. Like the flag down in an American letter box. You only look inside if the flag is up.

So - why not multiply a SUM() with a CASE expression?

And don't make quoted strings out of numbers ....

SELECT
  app_name
, user_id
, api
, SUM(nb) * CASE api
      WHEN 'v1.pt_ob'     THEN 0.7
      WHEN 'v1.place_uri' THEN 1
      WHEN 'V2'           THEN 0.4
      ELSE NULL
  END
FROM stat_compiled.requests_calls_y2022m02
WHERE app_name IN (
  'CMI_transilien'
, 'CMI - APM'
, 'Media_SNCF.com'
, 'Medias_TER'
, 'CMI PIV- sncf.com'
, 'CMI PIV - TER'
)
GROUP BY 
  app_name
, api
, user_id
;