I have a table that contains dozens of athletes and the hours they spent at the gym everyday. My goal is to calculate their total hours in a month. They were supposed to go every day, and if they missed any day (null for that day), I want the entire total hours column for that athlete to display null, not the sum of their monthly hours. I have two columns, daily_hours, and total_hours, which is defined as sum(daily_hours). My query is this, however it is giving me an error in syntax
select case
when daily_hours is null then total_hours = null
else total_hours end as name, total_hours
from (select name, sum(daily_hours) as total_hours from athletes group by name);
CodePudding user response:
You have a few issues, your case expression is incorrect and you're missing an alias for your derived table - however you can simplify by using a case expression in combination with coalesce to determin your NULL values:
select name,
case when
Min(Coalesce(daily_hours,-1)) > 0 then Sum(daily_hours)
else
null
end as total_hours
from athletes
group by name;