Multiple Values Count Once - SQL Server Forums

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]

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

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/

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

;

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources