CODEWITHSUNDEEP
sqlsql-server
Daniel
Daniel

Reputation: 393

Group data based on case statements in SQL Server?

I have a table of data and I would want to group 2 columns based on a logic formed from a few case statements in a new column. This is my data:

enter image description here

And this is my current sql:

select a.Action,st.State,ym.Year,sum(RatingCount) as LevelCount
from ActionTable a
left join StateTable st on a.ID = st.ActionID
left join YearMetrics ym a.Name = ym.NameCategory and st.Name = ym.CategoryName
group by a.name,st.name,ym.Year,ym.Level

These are the case statements (not all of them) base on which the logic should apply:

case when level = 'high' and levelcount >= 1 then 'High'
case when level = 'medium' and levelcount >3 then 'High'
else Low
end as Level

So, for example in case of Oregon (lines 20,21,22) I would want, based on the case statements to group the data on Action, State, Year. A new column named Level should be formed based from the logic on the case statements. So in the case of line 20, because there is no case statements to match the data in the table the result should be:

Non-Travel Oregon 2020 Low

The lines 21,22 should be:

Non-Travel Oregon 2021 High

because, according to the case statements, there is one levelcount >=1 and Level is High. In the case of line 19 the result should be :

Non-Travel Nevada null null

What I have tried includes:

I have not managed to obtain the desired result using any of the techniques.

This is the expected result:

enter image description here

Any help would be appreciated.

Upvotes: 0

Views: 52

Answers (2)

Steve Lovell
Steve Lovell

Reputation: 2564

As far as I can tell, the stated expected results are not compatible with what you've give us in terms of rules of how to derive them. It also doesn't help that your data rather than being the raw data is the output of your existing query. As a result, it feels like we're guessing a bit here ...

The query I've given below doesn't return what you say you want, but it's close and I think agrees with your explanation.

WITH subquery AS
(
  select a.Action,st.State,ym.Year,ym.Level,sum(RatingCount) as LevelCount
  from ActionTable a
  left join StateTable st on a.ID = st.ActionID
  left join YearMetrics ym a.Name = ym.NameCategory and st.Name = ym.CategoryName
  group by a.name,st.name,ym.Year,ym.Level
) --This is just your original code with ym.Level added to the SELECT clause.

SELECT
  s.Action,
  s.State,
  s.Year,
  CASE WHEN s.Level = 'high'   AND s.LevelCount >=1 THEN 'High'
       WHEN s.Level = 'medium' AND s.LevelCount  >0 THEN 'High'
       WHEN s.Level IS NULL THEN NULL --If you don't do this, NULLs become 'Low'
       ELSE 'Low'
  END AS NewLevel
FROM
  subquery s
GROUP BY
  s.Action,
  s.State,
  s.Year,
  CASE WHEN s.Level = 'high'   AND s.LevelCount >=1 THEN 'High'
       WHEN s.Level = 'medium' AND s.LevelCount  >0 THEN 'High'
       WHEN s.Level IS NULL THEN NULL
       ELSE 'Low'
  END

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269533

This appears to be the logic that you are describing:

select a.Action, st.State, ym.Year,
       sum(RatingCount) as LevelCount,
       (case when level = 'high' and sum(RatingCount) >= 1 then 'High'
             when level = 'medium' and sum(RatingCount) > 3 then 'High'
             when level = 'medium' then 'Low'
        end) as Level
from ActionTable a left join
     StateTable st
     on a.ID = st.ActionID left join
     YearMetrics ym
     on a.Name = ym.NameCategory and st.Name = ym.CategoryName
group by a.name, st.name, ym.Year, ym.Level;

Upvotes: 1

Related Questions