Help with SELECT DISTINCT

[Annalyzer]

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

SELECT count(DISTINCT hhId) FROM food_box;

but

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.

[sphinney]

Annalyzer:

Does this give you what you are looking for:

select count(hhid) from food_box group by hhid

sphinney

@Annalyzer

[Annalyzer]

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.

[Annalyzer]

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?

[sphinney]

Annalyzer:

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

SELECT Count([Sub_query].hhId) AS Count_Of_Households
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

[Annalyzer]

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.