CODEWITHSUNDEEP
sqlaggregate
Kiran
Kiran

Reputation: 11

Need SQL query to find count

Household          Individual
101                      11
101                      12
101                      13
101                      14
101                      15
102                      1
102                      2
102                      3
102                      4

I need a SQL query to get the number of households with more than 4 individuals. In the above table, only household 101 has more than 4 individuals. So the extract should be 1.

Upvotes: 1

Views: 113

Answers (8)

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

I think you need: 

SELECT COUNT(Household) AS HouseholdsWithMoreThan4Individuals
FROM (
    SELECT Household
    FROM [MytTable]
    GROUP BY Household
    HAVING COUNT(Individual) > 4) Res;

In this case the result will be 1 - the result you require - because there is only one household with more than 4 individuals.

You need to perform two operations: first you need to get a list of all households with more than 4 individuals. After that you need to count those households. You need to be careful not to count any household more than once.

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Maybe something like this:

SELECT COUNT(Household)
FROM
(
 SELECT Household
 FROM TABLE1
 GROUP BY Household
 HAVING Count(Household) > 4
) Tmp

FIDDLE DEMO

Upvotes: 1

Jens
Jens

Reputation: 69440

If I understand your question correctly, this should give you the right value (1).

select count(*) from (select Household  from table group by Household  having count(*)>4)

Upvotes: 0

J0e3gan
J0e3gan

Reputation: 8938

Initial Answer

Of course a demonstration requires choosing a SQL implementation, my choice being T-SQL; but the solution is pretty generalizable to other SQL implementations:

declare @householdInfo table (Household int, Individual int);

insert into @householdInfo (Household, Individual)
select 101, 11
union
select 101, 12
union
select 101, 13
union
select 101, 14
union
select 101, 15
union
select 102, 1
union
select 102, 2
union
select 102, 3
union
select 102, 4;

--select * from @householdInfo; -- FORNOW: for sanity checking

-- NOTE: what you basically need - that yields 1 like you expect
select COUNT(*)
from (
    select Household, COUNT(Individual) IndividualCount
    from @householdInfo
    group by Household
    having COUNT(Individual) > 4) s;

The important parts are that you need to:

  1. Select the COUNTs of Individuals in the inner query.
  2. Group the COUNTs of Individuals on Household values.
  3. Use the aggregate equivalent of WHERE - HAVING - to filter the inner query's results (i.e. > 4).
  4. Select the final COUNT in the outer query.

Follow-On

@VigneshKumar and @GiannisParaskevopoulos make a good point - that you technically do not need COUNT in the inner query's column list.

Another (ugly) subtlety exists in the wild, may apply to your situation, and necessitates not only COUNT(Individual) in the inner query('s HAVING clause) but further leveraging a distinct count:

select COUNT(Household) -- yes, prefer Household over * for clarity
from (
    select Household
    from @householdInfo
    group by Household
    -- distinct added if duplicates are an issue
    having COUNT(distinct Individual) > 4) s;

A simple change from the sample data you provided to...

Household    Individual
---------    ----------
101          11
101          12
101          13
101          14
101          15
102          1
102          2
102          3
102          4
102          4 -- NOTE: duplicate row to demonstrate the point

...makes the distinction (pun intended) clear (i.e. that the 1st query I provided would yield 2 in this case whereas the 2nd query I provided would still yield 1).

Upvotes: 0

Zam
Zam

Reputation: 2940

select 
    distinct Household
from 
    Table_2
where
    Individual > 4

Result will be 101.

If you need count (not number as above), then

select 
    COUNT (Household) as E1
from 
    Table_2
where
    Individual > 4

Upvotes: 0

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT COUNT(*)
FROM 
(
    SELECT Household 
    FROM   TableName 
    GROUP BY Household 
    HAVING COUNT(Household) > 4
) AS T

Upvotes: 1

realnumber3012
realnumber3012

Reputation: 1062

SELECT household FROM YourTable
GROUP BY household
HAVING COUNT(DISTINCT individual) > 4

Upvotes: 0

Jerodev
Jerodev

Reputation: 33186

SELECT Household, count(Household) as cnt FROM {{TABLE}} 
GROUP BY Household
WHERE cnt > 4

Upvotes: 0

Related Questions