CODEWITHSUNDEEP
sqlsql-servert-sql
Pedro Ribeiro
Pedro Ribeiro

Reputation: 9

How to aggregate results on a single row changing with "case when"

I have the following issue:

Select owner name,  animal,
From DB


John   cat     
John   dog     
John   Lion
John   Tiger

I would like to filter by domestic animal and wild ones so:

Select name, 
       (case when animal like ('cat','dog') then 'domestic' else 'wild' end) as type
from DB   



John Domestic
John Domestic  
John Wild
John Wild

I would like something like:

John   2 Domestic
John   2 Wild

How to do it?

Thank you.

Upvotes: 0

Views: 61

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81970

Just another three options

Select [Owner Name]
      ,[Type]       = case when animal in ('cat','dog') then 'Domestic' else 'Wild' end
      ,Cnt          = Count(*)
 From  DB
 Group By [Owner Name]
         ,case when animal in ('cat','dog') then 'Domestic' else 'Wild' end

Or if you rather NOT replicate the CASE

Select *
      ,Cnt = count(*)
 From  (
        Select [Owner Name]
              ,[Type]       = case when animal in ('cat','dog') then 'Domestic' else 'Wild' end
         From  DB
       ) src
 Group By [Owner Name]
         ,[Type]

As CharlieFace pointed out, CROSS APPLY !!

Select A.[Owner Name]
      ,B.[Type]
      ,Cnt = count(*)
From  DB A
Cross Apply ( values ( case when animal in ('cat','dog') then 'Domestic' else 'Wild' end)  ) B([Type])
Group By A.[Owner Name]
        ,B.[Type]

Results

Owner Name  Type        Cnt
John        Domestic    2
John        Wild        2

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270021

If I understand correctly, just use aggregation:

Select name, 
       (case when animal like ('cat','dog') then 'domestic' else 'wild' end) as type,
       count(*)
from DB  
group by name, (case when animal like ('cat','dog') then 'domestic' else 'wild' end);

Upvotes: 0

Jhamman Sharma
Jhamman Sharma

Reputation: 167

select 
      name ,  
      IF(animal = 'dog' ||  animal = 'cat' , 'Domestic', 'Wild') as type , 
      count(*) 
FROM animals 
GROUP BY  name , type

Upvotes: -1

Related Questions