| Author |
 Topic  |
|
|
Harris00
Starting Member
19 Posts |
 Posted - 03/07/2013 : 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
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 03/07/2013 : 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 - 03/07/2013 : 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
India
47023 Posts |
Posted - 03/08/2013 : 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
Australia
52 Posts |
Posted - 03/08/2013 : 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
India
47023 Posts |
Posted - 03/08/2013 : 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/
|
|
|
| |
Topic |
|
|
|
Multiple Values Count Once
