SQL CASE and Union

Sreeni P 0
Viewed 41

any idea what is wrong with this statement. I am just trying to get grades from three different tables and take an average of that. then assign a pass/fail based on the average value.

CREATE OR REPLACE MODEL `bqml_test.ModelTest`
OPTIONS(model_type='logistic_reg') AS
SELECT *, 
  AVG(combined.label) as average 
CASE WHEN average > 0.4 then ‘pass’
  ELSE ‘fail’
  END as success
FROM (SELECT * FROM `interns2022.uniqueIdGrades.1617` 
UNION ALL SELECT * FROM `interns2022.uniqueIdGrades.1718`
UNION ALL SELECT * FROM `interns2022.uniqueIdGrades.1819`
UNION ALL SELECT * FROM `interns2022.uniqueIdGrades.1920`)
CASE
WHEN
Course_Grade = "A" then 1
WHEN
Course_Grade = "B" then 0.8
WHEN
Course_Grade = "C" then 0.6
WHEN
Course_Grade = "D" then 0.4
WHEN
Course_Grade = "F" then 0.2
Else 0
End as label as combined
GROUP BY unique_identifier
union case sql
1 Answers

I found it hard to read the SQL you had up so I did the best job I could to try to recreate what you were going for. I also don't know what DBMS you are using. Hopefully this helps get you on a good track.

I created a CTE, like a virtual table with all your intern tables to put them all into one set. Then used a select statement to get the data from them. You should be able to apply your case logic, however you will need to put an outer query if you want to average up all the grades. HTH.

WITH CTE_UNIONGRADES
AS
(

SELECT * FROM interns2022.uniqueIdGrades.1617
UNION ALL
SELECT * FROM interns2022.uniqueIdGrades.1718
UNION ALL
SELECT * FROM interns2022.uniqueIdGrades.1819
UNION ALL
SELECT * FROM interns2022.uniqueIdGrades.1920
)

SELECT
Z.Unique_Identifier
, CASE WHEN AVG(Z.label_combined) > 0.4 THEN 'Pass' ELSE 'Fail' END AS SuccessStatus
FROM
(
SELECT Unique_Identifier
, CASE WHEN Course_Grade = 'A' then 1
     WHEN Course_Grade = 'B' then 0.8
     WHEN Course_Grade = 'C' then 0.6
     WHEN Course_Grade = 'D' then 0.4
     WHEN Course_Grade = 'F' then 0.2
     Else 0 End as label_combined

FROM 
CTE_UNIONGRADES
) Z

GROUP BY 
Z.Unique_Identifier
Vlookup0
Related