unique records - 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

General SQL Server Forums

New to SQL Server Programming

unique records

Author

Topic

overboard22
Starting Member

13 Posts

Posted - 2012-04-27 : 15:54:13

hello! so i have a table like this:
fields are: PRISON ID GENRE CRIME
---
AK 1 Male 01
AK 1 Male 02
AL 2 Female 03
AL 2 Female 04
AD 3 Female 05
AD 4 Male 01
AD 5 Male 03

my query should return something like this:
fields: PRISON PRISONERS MALE FEMALE
-----
AK 1 1 0
AL 1 0 1
AD 3 2 1

now i know to count unique records from ID i just use:
select ID, count(DISTINCT ID) AS PRISONERS.. the PRISONERS column is taken care of.

the thing is, i need to get the count of genre too but excluding the duplicate records as well. when i try to run my current query it just returns something like this:

fields:PRISON PRISONERS MALE FEMALE
----
AK 1 2 0
AL 1 0 2
AD 3 2 1

which, clerly is wrong, cos its just giving me all the fields that have MALE and FEMALE, when i know there has to be a 1 in both rows cos theres only one prisoner.

i was just wondering how i could get results like the first table there.. my current query is something likethis:
select ID, count(DISTINCT ID) AS PRISONERS, count(case when genre='male') AS MALE from... but like i said, i know thats wrong.

thank you so much for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 15:58:57

[code]
select ID, count(DISTINCT ID) AS PRISONERS,
COUNT(DISTINCT case when GENRE ='Male' THEN ID ELSE NULL END) AS MALE,
COUNT(DISTINCT case when GENRE ='Female' THEN ID ELSE NULL END) AS FEMALE
FROM Table
GROUP BY ID
[/code]

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

overboard22
Starting Member

13 Posts

Posted - 2012-04-27 : 16:04:30

thank you so so so much! worked perfectly, youre a life saver :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 16:06:55

welcome

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

arya.basu
Starting Member

1 Post

Posted - 2012-04-29 : 23:31:12

I think the above query will not show the result like below
PRISON PRISONERS MALE FEMALE
-----
AK 1 1 0
AL 1 0 1
AD 3 2 1

the result will like this
ID PRISONERS MALE FEMALE
1 1 1 0
2 1 0 1
3 1 0 1
4 1 1 0
5 1 1 0

So, I think the proper query should be
SELECT
PRISON
,COUNT(DISTINCT ID) PRISONERS
,COUNT(DISTINCT CASE WHEN GENRE='Male' THEN ID ELSE NULL END) AS MALE
,COUNT(DISTINCT CASE WHEN GENRE='Female' THEN ID ELSE NULL END) AS FEMALE
FROM
table1
GROUP BY
PRISON

Arya Basu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 10:01:35

i just copy pasted it from OPs original suggestion. It was having ID so I thought thats what OP was interested in

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources