By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,967 Members | 1,241 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,967 IT Pros & Developers. It's quick & easy.

Help with SELECT DISTINCT

100+
P: 122
I have a table that counts food boxes received by each household (hhId).

Table Name: food_box
Fields: fbId (PK), fbDate, hhId (FK)

Currently, the table holds the following data:

fbId,Date,hhId
1,1 /1 /2009,1
2,2 /1 /2009,1
3,3 /1 /2009,1
4,4 /1 /2009,1
5,5 /1 /2009,1
6,6 /1 /2009,1
7,12/1 /2008,1
8,7 /1 /2009,1
9,1 /15/2009,1
10,2 /15/2009,1
11,3 /15/2009,1
17,7 /5 /2009,1
18,4 /15/2009,1
19,7 /7 /2009,2
20,7 /7 /2009,2
21,7 /7 /2009,2

I want a query that gives me a count of the unduplicated households that received a food box (answer should be 2).

It won’t let me do
Expand|Select|Wrap|Line Numbers
  1. SELECT count(DISTINCT hhId) FROM food_box;
but
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT count(hhId) FROM food_box;
gives me a count of 15.

I don't know why this is so difficult for me. Must be lunch time. Thanks in advance for any help.
Jul 7 '09 #1
Share this Question
Share on Google+
5 Replies


P: 69
Annalyzer:

Does this give you what you are looking for:

Expand|Select|Wrap|Line Numbers
  1. select count(hhid) from food_box group by hhid
sphinney

@Annalyzer
Jul 7 '09 #2

100+
P: 122
Ohhhhhhhhhhhhh, of course! Boy do I feel dumb. I was trying to make it harder than it really was. I had the DISTINCT keyword stuck in my brain when I didn't even need it.

Thanks so much! Back on track now.
Jul 7 '09 #3

100+
P: 122
Oops! Wait a second. That gives me the total number of food boxes for each year. I need the unduplicated count of households for each year.

Anybody?
Jul 7 '09 #4

P: 69
Annalyzer:

Oops. My bad. I should have tested my suggestion before making it. Try the following. I've tested this and it DOES work!

Expand|Select|Wrap|Line Numbers
  1. SELECT Count([Sub_query].hhId) AS Count_Of_Households
  2. FROM (select hhId from food_box group by hhId)  AS [Sub_query]
What you're trying to do is really a two step process. First you're trying to find the unique household ID's (hhid). This is accomplished with a subquery. Second, you're wanting to count them.

Regards,
sphinney

@Annalyzer
Jul 8 '09 #5

100+
P: 122
That explains why I was able to do it with 2 queries. I like you're solution much better, though, thank you. It's much neater.
Jul 9 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.