CODEWITHSUNDEEP
sqlteradata
Lisa
Lisa

Reputation: 3181

aggregate function error in case expression

I have this query

SELECT mylearning.Employee_Id,
 case 
        when max(case when not mylearning.CourseStatusTXT = 'Completed' then 1 else 0 end) = 0 then '2018 Complete'
        when max(case when mylearning.CourseStatusTXT in ('Started', 'Not Started') then 1 else 0 end) = 1 then '2018 Not Complete'
  end as Completion_Status
FROM  Analytics.myLearning_Completions as mylearning inner join Analytics.Workday WD on mylearning.Employee_ID = WD.Employee_ID

And I want to add a condition to the first when statement to make it like this

 when max(case when not mylearning.CourseStatusTXT = 'Completed' then 1 else 0 end) = 0 
             and WD.Adjusted_Hire_Date like '2019% '
            and mylearning.CourseTimeCompletedH < cast (WD.Adjusted_Hire_Date as date format 'YYYY/MM/DD') +7
             then '2018 Complete'

but I keep getting this error

Executed as Single statement.  Failed [3504 : HY000] Selected non-aggregate values must be part of the associated group. 
Elapsed time = 00:00:00.069

How can I fix it?

Upvotes: 0

Views: 78

Answers (3)

ravioli
ravioli

Reputation: 3833

Like a couple others mentioned, you are trying to mix grouped data with non-aggregated data in your calculation, which is why you're getting the 3504 error. You need to either include the referenced columns in your GROUP BY or include them inside an aggregate function (i.e. MAX).

I'm not 100% sure if this is what you're after, but hopefully it can help you along.

SELECT 
  mylearning.Employee_Id,
  CASE
    WHEN 
      MAX(CASE WHEN NOT mylearning.CourseStatusTXT = 'Completed' THEN 1 ELSE 0 END) = 0 AND
      WD.Adjusted_Hire_Date like '2019% ' AND
      -- Check if most recently completed course is before Hire (Date + 1 week)
      MAX(mylearning.CourseTimeCompletedH) < 
      CAST(WD.Adjusted_Hire_Date AS DATE FORMAT 'YYYY/MM/DD') + 7 
    THEN '2018 Complete' -- No incomplete learnings
    WHEN MAX(
      CASE WHEN mylearning.CourseStatusTXT IN ('Started', 'Not Started') THEN 1 ELSE 0 END
    ) = 1 THEN '2018 Not Complete' -- Started / Not Started learnings exist
  END AS Completion_Status
FROM Analytics.myLearning_Completions as mylearning  -- Get learning info
INNER JOIN Analytics.Workday WD on mylearning.Employee_ID = WD.Employee_ID -- Employee info
GROUP BY mylearning.Employee_Id, WD.Adjusted_Hire_Date

This will give you a summary per employee, with a couple assumptions:

  1. Assuming employee_ID value in Analytics.Workday is a unique value (one-to-one join), to use WD.Adjusted_Hire_Date in your comparisons, you just need to include it in the GROUP BY.

  2. Assuming you have multiple courses per employee_Id, in order to use mylearning.CourseTimeCompletedH in your comparisons, you'd need to wrap that in an aggregate like MAX.

The caveat here is that the query will check if the most recently completed course per employee is before the "hire_date" expression, so I'm not sure if that's what you're after.

Give it a try and let me know.

Upvotes: 1

access_granted
access_granted

Reputation: 1917

Maybe this is what you want?

SELECT 
  mylearning.employee_id
  , case 
    when CourseStatusTXT = 'Completed' and WD.Adjusted_Hire_Date like '2019%' 
      and mylearning.CourseTimeCompletedH < cast (WD.Adjusted_Hire_Date as date format 'YYYY/MM/DD') +7
    then '2018 Complete'
    else '2018 Not Complete'
  end CompletionStatus
FROM  myLearning_Completions mylearning, Workday WD 
WHERE mylearning.employee_id = WD.employee_id

Upvotes: 0

mwelcome
mwelcome

Reputation: 1

The issue here is that you are mixing detail row by row information in the same query as group or aggregated data. Aggregated data will output a single value for all the rows unless you have a group by clause. If you have a group by clause then it will output a single value for each group. When you are grouping you can also include any values that are in the group by clause since they will be unique for the group.

if you want this data for each employee, then you could group by employee_id. Any other data would need to also be an aggregate like Max(Adjusted_Hire_Date)

Upvotes: 0

Related Questions