SQL CASE WHEN ELSE not working in AWS Athena-CodePudding

I have the script below setup in AWS Athena, the goal is to replace some budget numbers (total) with 0 if they are within a certain category (costitemid). I'm getting the following error in AWS Athena and could use some advice as to why it isn't working. Is the problem that I need to repeat everything in the FROM and GROUP BY in the WHEN and ELSE? Code below the error. Thank you!

SYNTAX_ERROR: line 6:9: 'projectbudgets.projectid' must be an aggregate expression or appear in GROUP BY clause This query ran against the "acorn-prod-reports" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 077f007b-61a0-4f6b-aa1f-dd38bb401218

SELECT
CASE
    WHEN projectbudgetlineitems.costitemid IN (462561,462562,462563,462564,462565,462566,478030) THEN (
        SELECT
        projectbudgets.projectid
        , projectbudgetyears.year fiscalYear
        , projectbudgetyears.status
        , "sum"(((0 * projectbudgetlineitems.unitcost) * (projectbudgetlineitems.costshare * 1E-2))) total
    )
    ELSE (
        SELECT
        projectbudgets.projectid
        , projectbudgetyears.year fiscalYear
        , projectbudgetyears.status
        , "sum"(((projectbudgetlineitems.quantity * projectbudgetlineitems.unitcost) * (projectbudgetlineitems.costshare * 1E-2))) total
    )
END
FROM
  (("acorn-prod-etl".target_acorn_prod_acorn_projectbudgets projectbudgets
INNER JOIN "acorn-prod-etl".target_acorn_prod_acorn_projectbudgetyears projectbudgetyears ON (projectbudgets.id = projectbudgetyears.projectbudgetid))
INNER JOIN "acorn-prod-etl".target_acorn_prod_acorn_projectbudgetlineitems projectbudgetlineitems ON (projectbudgetyears.id = projectbudgetlineitems.projectbudgetyearid))
--WHERE (((projectbudgetlineitems.costitemid <> 478030) AND (projectbudgetlineitems.costitemid < 462561)) OR (projectbudgetlineitems.costitemid > 462566))
GROUP BY projectbudgets.projectid, projectbudgetyears.year, projectbudgetyears.status

CodePudding user response：

Your syntax is wrong (at least according to most SQL dialects.) You can't generally say "SELECT CASE WHEN (condition) THEN (this select clause) ELSE (that select clause) END FROM (tables)"

You can only use CASE to calculate a single value.

But it looks as if the only change between your two inner SELECT clauses is whether you use 0 or the quantity in the final multiplication. And that is perfect for a CASE!

I do not guarantee this will work right off the bat, because I don't have your setup or an idea of your table layout. However, it's a step in the right direction:

SELECT
  projectbudgets.projectid
, projectbudgetyears.year fiscalYear
, projectbudgetyears.status
, "sum"(
    ((
       CASE
         WHEN projectbudgetlineitems.costitemid IN (462561,462562,462563,462564,462565,462566,478030) 
         THEN 0
         ELSE projectbudgetlineitems.quantity
         END * projectbudgetlineitems.unitcost
     ) * (
       projectbudgetlineitems.costshare * 1E-2
     ))) total
   
FROM
  (("acorn-prod-etl".target_acorn_prod_acorn_projectbudgets projectbudgets
    INNER JOIN 
    "acorn-prod-etl".target_acorn_prod_acorn_projectbudgetyears projectbudgetyears 
       ON (projectbudgets.id = projectbudgetyears.projectbudgetid))
    INNER JOIN "acorn-prod-etl".target_acorn_prod_acorn_projectbudgetlineitems projectbudgetlineitems 
       ON (projectbudgetyears.id = projectbudgetlineitems.projectbudgetyearid))
  GROUP BY 
    projectbudgets.projectid, projectbudgetyears.year, projectbudgetyears.status

CodePudding user response：

This could solve your problem if you want to sum the items for each project and year and status except for certain line items. Here, it is correct to use a "where" condition and not "case when" :

SELECT
projectbudgets.projectid, 
projectbudgetyears.year, 
projectbudgetyears.status,
"sum"(((projectbudgetlineitems.quantity * projectbudgetlineitems.unitcost) * 
(projectbudgetlineitems.costshare * 1E-2))) total
FROM
  (("acorn-prod-etl".target_acorn_prod_acorn_projectbudgets projectbudgets
INNER JOIN "acorn-prod-etl".target_acorn_prod_acorn_projectbudgetyears 
 projectbudgetyears ON (projectbudgets.id = projectbudgetyears.projectbudgetid))
INNER JOIN "acorn-prod-etl".target_acorn_prod_acorn_projectbudgetlineitems 
 projectbudgetlineitems ON (projectbudgetyears.id = 
  projectbudgetlineitems.projectbudgetyearid))
WHERE projectbudgetlineitems.costitemid NOT IN 
 (462561,462562,462563,462564,462565,462566,478030) 
GROUP BY projectbudgets.projectid, projectbudgetyears.year, 
 projectbudgetyears.status

;