sql nested queries - case-CodePudding

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 ?

CodePudding user response：

You need to give the result of the CASE expression an alias (giving the column a name).

Also, don't use keywords such as SUM (or CASE) as aliases.

SELECT
  app_name, user_id, api, sum_nb*case_expression
from
(
  select
    SUM("nb")   AS sum_nb,
    app_name,
    user_id,
    api,
    CASE
      WHEN api='v1.pt_objects' THEN '0.7'
      WHEN api='v1.place_uri'  THEN '1'
                               ELSE 'autre'
    END
      AS case_expression
  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

Even then, it still won't work.

sum_nb is a numeric type
case_expression is a string
you can't multiply numbers by strings
So, what are you trying to do?

Side Note: Your chain of OR conditions can simplify to an IN() expression...

  WHERE
    app_name IN (
      'CMI_transilien',
      'CMI - APM',
      'Media_SNCF.com',
      'Medias_TER',
      'CMI PIV- sncf.com',
      'CMI PIV - TER'
    )

CodePudding user response：

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
;