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
 General SQL Server Forums
 New to SQL Server Programming
 unique records		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

overboard22
Starting Member

13 Posts
Posted - 04/27/2012 :  15:54:13  Show Profile  Reply with Quote
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!
Edited by - overboard22 on 04/27/2012 15:55:20

visakh16
Very Important crosS Applying yaK Herder

India
48012 Posts
Posted - 04/27/2012 :  15:58:57  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

overboard22
Starting Member

13 Posts
Posted - 04/27/2012 :  16:04:30  Show Profile  Reply with Quote
thank you so so so much! worked perfectly, youre a life saver :D
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48012 Posts
Posted - 04/27/2012 :  16:06:55  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

arya.basu
Starting Member

India
1 Posts
Posted - 04/29/2012 :  23:31:12  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48012 Posts
Posted - 04/30/2012 :  10:01:35  Show Profile  Reply with Quote
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/
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