CODEWITHSUNDEEP
sql-server-2008group-by
Andy M
Andy M

Reputation: 6065

How to regroup records with different values in one SQL query

Let's say I have the following table :

Name - Country - Age
--------------------
Toto - Switzerland - 10
Titi - France - 12
Tata - Italy - 21
Tutu - England - 13
Tete - Italy - 14

I want to create a sql query as simple as possible to regroup people living in defined grouped countries like :

Group A = Switzerland + Italy Group B = France + England

I don't know how to create a group withn my records with a column that could have multiple different values in the same group...

Could somebody help me with this ?

More information : SQL Server 2008 database.

Upvotes: 1

Views: 1627

Answers (2)

JNF
JNF

Reputation: 3730

You mean like this?

SELECT COUNT(Name), GroupA, GroupB FROM
   (`SELECT Name, Country, Age,
   Country='Switzerland' OR Country='Italy' As GroupA,
   Country='France' OR Country='England' As GroupB)
Group By GroupA, GroupB

Upvotes: 4

ejb_guy
ejb_guy

Reputation: 1125

             Select * from (select *,case when Country ='Switzerland' then 'A'
                         when Country ='Italy' then 'A'  
                         when Country ='France' then 'B'  
                         when Country ='England' then 'B'  
                    else 'C' end) classification from table1)
         order by classification

This will group the ppl as per your criteria. If this grouping is static you can have seprate table and use inner join. That will make query more readable

Upvotes: 2

Related Questions