CODEWITHSUNDEEP
sqlsql-server
Tejaswi Pandava
Tejaswi Pandava

Reputation: 506

SQL Group By Query to return the output in a desired format

I have a table detailtable like below where Value is Int and rest of the column are Varchar

+------+------+--------+---------+-------+
| Name | City | postal | country | value |
+------+------+--------+---------+-------+
| Adam | hyd  | 500000 | India   |    10 |
| eve  | hyd  | 500000 | India   |    20 |
| Adam | Aus  | 200000 | India   |    30 |
| Adam | Aus  | 200000 | India   |    40 |
| Scott| Aus  | 400000 | India   |    90 |
+------+------+--------+---------+-------+

I want to write a Query such that i get the following output

+---------+------+--------+---------+
|  Name   | City | postal | country |
+---------+------+--------+---------+
| Various | hyd  | 500000 | India   |
| Adam    | Aus  | 200000 | India   |
| Scott   | Aus  | 400000 | India   | 
+---------+------+--------+---------+

When 2 records have same city, postal,country, Name should be Various

and when Name, city, postal and country are same then name should be actual name

In case we have distinct city, postal, country pair then name will be actual name

select City,Postal,country from detailtable group by City,Postal,country having count(*)>1

returns me the data set (City,Postal,country) which are having more than one occurance.

Upvotes: 0

Views: 74

Answers (2)

Popeye
Popeye

Reputation: 35900

You can use the case expression with count as follows:

select  count(distinct name) = 1 then min(name) else 'various' end as name, 
        city, postal, country
from detailtable t
group by City, Postal, country;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Use a case expression with aggregation functions:

select (case when min(name) = max(name) then min(name)
             else 'various'
        end) as name, City, Postal, country
from detailtable
group by City, Postal, country ;

Upvotes: 1

Related Questions