SQL SELECT with GROUP BY, referring back to the group

Question

I'm a relative SQL beginner, and I'm not entirely sure how to ask what I'm asking (so I'm also not entirely sure how to search for help on it).

I want to use a SELECT statement that uses a GROUP BY clause to group by a certain attribute, and at the same time refer back to the rows selected by that group. Is there any way to do this? (I'm using, FWIW, SQLite3, but I reckon this is more a question of universal SQL syntax.)

I have a table population in which I have data about people and households. Each row is a person, and the people are each grouped into households of anywhere from one to a dozen.

One person in each household (generally the first person, by uid) is the head of the household, and has the head column set. I want to GROUP BY the column household_id, and also select the uid of each member of the group who is the "head". Is there any way to do that all with one statement?

Here's what I have:

SELECT DISTINCT household_id,
    COUNT(uid) AS members   /* The number of members in each household; it works */,
    (SELECT uid FROM "group" WHERE head == 1) /* Only I don't know how to do this */
    FROM population GROUP BY household_id;

I tried something like this, and it didn't give me what I want:

SELECT DISTINCT household_id,
    COUNT(uid) AS members,
    (SELECT uid FROM population WHERE household_id == household_id AND head == 1) /* <-- here */
    FROM population GROUP BY household_id;

But that of course picks the first row in which household_id is household_id; i.e. the very first row in the table. How do I refer to only the household_id that is DISTINCT in this particular group?

Any tips? Thanks in advance.

Answer 1

give this a try,

SELECT  household_id,
        COUNT(uid) AS members, 
        MAX(CASE WHEN head = 1 THEN uid ELSE NULL END) headID
FROM    population 
GROUP   BY household_id;

• SQLFiddle Demo