Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Multiple Values Count Once

Author  Topic 

Harris00
Starting Member

19 Posts

Posted - 2013-03-07 : 17:30:28
I have a scenario, where client can have more than one Race like below

ClientId Client Name Client Datebirth Client Race
100 John, Smith 01/01/1980 White
100 John, Smith 01/01/1980 AmericanIndian
101 John, Dose 03/01/1977 Black
102 Doe, John 01/23/1983 Asian
102 Doe, John 01/23/1983 White

I would like to know how to show the client in only one group, for example if client got counted in White then i dont want him to again appear under american indian

White
100 John, Smith 01/01/1980
102 Doe, John 01/23/1983
Count: 2
Black
101 John, Dose 03/01/1977 Black
Count: 1

AmericanIndian
Count: 0

Asian
Count: 0


Appreciate your guidance and feedback

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-07 : 17:43:23
If you don't care about which of the multiple races are used, you can use an aggregate function such as MAX as shown below:
SELECT
ClientID,
[Client Name],
[Client Datebirth],
MAX([Client Race]) AS [Client Race]
FROM
TheTable
GROUP BY
ClientID,
[Client Name],
[Client Datebirth]
Go to Top of Page

Harris00
Starting Member

19 Posts

Posted - 2013-03-07 : 22:44:03
How would i change the query if i had count the race in specific sequence.
Like
White
Black
Asian
AmericanIndian
Other

If i client has mulitple races like White and Asian then he is counted and showed only in Race Whitea and so on.

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 00:24:04
quote:
Originally posted by Harris00

How would i change the query if i had count the race in specific sequence.
Like
White
Black
Asian
AmericanIndian
Other

If i client has mulitple races like White and Asian then he is counted and showed only in Race Whitea and so on.

Thanks


you would need a separate sequence table for race in that case

define your sequence inside it with fields sequenceid and race

like

1 White
2 Black etc

then use like

SELECT ClientID,
[Client Name],
[Client Datebirth],
[Race] AS ClientRace
FROM
(
SELECT
ClientID,
[Client Name],
[Client Datebirth],
MIN(s.SequenceID) AS [Client Race ID]
FROM
TheTable t
JOIN SequenceTable s
ON s.Race = t.[Client Race]
GROUP BY
ClientID,
[Client Name],
[Client Datebirth]
)t
INNER JOIN SequenceTable s
ON s.SequenceID= t.[Client Race ID]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-03-08 : 04:54:52
-- Change ID for race order
With A as
(Select 1 as ID,'White' as Race union Select 2 as ID,'AmericanIndian' union Select 3 as ID,'Black' union Select 4 as ID,'Asian')
Select Count(*),Race
FROM (
select ClientId ,Client_Name, Client_Datebirth ,Race
from (select ClientId ,Client_Name, Client_Datebirth , min(id) as MinID
from YourTable join A on Client_Race = Race
Group by ClientId ,Client_Name, Client_Datebirth) z JOIN A ON MinID = ID) z group by race

;
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-08 : 05:28:54
quote:
Originally posted by UnemployedInOz

-- Change ID for race order
With A as
(Select 1 as ID,'White' as Race union Select 2 as ID,'AmericanIndian' union Select 3 as ID,'Black' union Select 4 as ID,'Asian')
Select Count(*),Race
FROM (
select ClientId ,Client_Name, Client_Datebirth ,Race
from (select ClientId ,Client_Name, Client_Datebirth , min(id) as MinID
from YourTable join A on Client_Race = Race
Group by ClientId ,Client_Name, Client_Datebirth) z JOIN A ON MinID = ID) z group by race

;


if its for a adhoc need then fine

otherwise its better to keep it as a separate table rather than as a CTE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -