Nsryjdtyk

Please consider this Table:

Id           FullName           Gender

---------------------------------------

1            Tom Hanksi Junior     1

2            Tom Cruisi            2

3            Meril Strippi         2

4            Leo  Dicaprioi        1

5            Robert Deniroi        1

6            Al Pcinoi             1

7            Chanrilize theroni    2

8            Robert Green          1

9            Nicole Kidmani        2

10           Nicole Wagner         2

11           Peter Pan Green       1

12           Peter Viera           1

13           Peter J. Dark         2

14           Tom Henry             1

Gender Values are: 1 for Male and 2 for Female.

Now I want to create a Table for names and genders(It's assumed that every name should has one corresponding gender).

Name       Gender

-----------------

Tom         1

Meril       2

Leo         1

Rebert      1

Al          1

Charlize    2

Nicole      2

Peter       1

1)How can I apply GROUP BY on just Name part of full name and gender?

2)How can I get contradictions of names and genders. For example for Tom we have male and female values.

Thanks

A bad assumption I am making here is that the first name is the part in Name before first space.

then the first name is found from name as

select left(Name, charindex(' ',Name)-1)

you can group by on this and gender

select 

Name=left(Name, charindex(' ',Name)-1),Gender

from

yourTableName

group by

left(Name, charindex(' ',Name)-1),Gender

order by left(Name, charindex(' ',Name)-1),Gender

to find people with two genders and same name you can use

select 

     Name=left(Name, charindex(' ',Name)-1)

from

     yourTableName

group by

     left(Name, charindex(' ',Name)-1)

having count(distinct gender)>1

In case you want to use both together, possibly in scenario when you want to discard name which have two genders associated you can do something like below

; with NnG as 

(

select 

Name=left(Name, charindex(' ',Name)-1),Gender

from

yourTableName

group by

left(Name, charindex(' ',Name)-1),Gender

),

N2G as 

(

select 

     Name=left(Name, charindex(' ',Name)-1)

from

     yourTableName

group by

     left(Name, charindex(' ',Name)-1)

having count(distinct gender)>1

)



select * from nng left join n2g 

on nng.Name=N2G.name 

where n2g.name is null

Substring everything up to the first space as the first name. Group on the name and reduce to those names having a count of more than one distinct gender:

SELECT

  SUBSTRING(Name, 1, charindex(' ',Name)-1), Gender

FROM

  table

GROUP BY

  SUBSTRING(Name, 1, charindex(' ',Name)-1)

HAVING

  COUNT(DISTINCT gender) > 1

If you have 1000 males, count distinct gender will return 1, because ther eis only one gender in the set (male). If you have 1000 males and 200 females, count distinct gender will return 2 because there are 2 kinds of gender in the set (male and female). If you were to omit the DISTINCT keyword, then the count() would return 1000 for the first example and 1200 for the second (it would count all the non null items in the set, not the variations therein)