SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Multiple Values Count Once
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Harris00
Starting Member

19 Posts

Posted - 03/07/2013 :  17:30:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3559 Posts

Posted - 03/07/2013 :  17:43:23  Show Profile  Reply with Quote
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 - 03/07/2013 :  22:44:03  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 03/08/2013 :  00:24:04  Show Profile  Reply with Quote
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

Australia
54 Posts

Posted - 03/08/2013 :  04:54:52  Show Profile  Reply with Quote
-- 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

India
52309 Posts

Posted - 03/08/2013 :  05:28:54  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000