CODEWITHSUNDEEP
sqlsql-servercase
BrentH
BrentH

Reputation: 7

SQL Return rows if two columns meet certain criteria

I have a table where I need to count the number of rows depending on if certain conditions are met. The table has ID, Level, and Name. Level can be "1" or "2" and Number can be "AAA 100" with the number ranging between 100-199 or "AAA 500" with the number ranging from 500-599. An ID could have a Name associated to it from either range and I want to count how many are from from a range based on if the Level is "1" or "2" for each ID.

Example Table:

a count

What I need to do is if the Level column = 2 then only return a count of Name values that have a number greater than or equal to 500. And if the Level = 1 for an ID, then only return a count of Name values between 100-199, or less than 500.

The desired output would be:

enter image description here

Thank you for the help!

Upvotes: 0

Views: 758

Answers (2)

Pedro Lorentz
Pedro Lorentz

Reputation: 2326

Assuming that "AAA" could be any other letters and that you are not going to have more than one level per id:

declare @sample table(
   Id int,
   Level int,
   Name varchar(20)
)

insert into @sample values
(1, 2, 'AAA 100'),
(1, 2, 'AAA 111'),
(1, 2, 'AAA 500'),
(1, 2, 'AAA 505'),
(1, 2, 'AAA 550'),
(2, 1, 'AAA 100'),
(2, 1, 'AAA 111'),
(2, 1, 'AAA 500'),
(2, 1, 'AAA 505'),
(2, 1, 'AAA 550');

select id,
       sum(case when level = 2 and substring(name, 5, 3) >= '500' then 1
                when level = 1 and substring(name, 5, 3) < '500' then 1
                else 0
           end) count

from @sample
group by id;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Hmmm . . . I am thinking you want one row per id. If so, you can do this with a single aggregation and coalesce():

select id,
       coalesce(sum(case when level = 2 and name >= 'AAA 500' then 1 end),
                sum(case when level = 1 and name < 'AAA 500' then 1 end)
               ) as cnt
from t
group by id;

If you want both levels if both exist, then just add level to the group by.

Upvotes: 2

Related Questions