The Sum of people that have same age in SQL?-CodePudding

I'm taking a SQL class and I need help with a question.

I have the following table on a phpmyadmin server

patient (id, age)

with a total of 100 patients

I'm told to find the sum of people that have same age using SQL.

I wrote this query:

SELECT COUNT(age) 
FROM patient 
HAVING (COUNT(age) > 1);

but it returns 100 as a result and when I did this query and found the number of patients who have the same age for each age and calculated the count manually I found 78 and I checked it manually and it's indeed 78.

SELECT COUNT(*) 
FROM patient 
GROUP BY age 
HAVING (COUNT(*) > 1);

What's wrong with my code?

CodePudding user response：

Start with a subquery that gets the counts of people with each age. Then filter this to counts more than 1 so it's just people with the same age.

Then in the main query you use SUM() to add all these counts together, to get the total number of patients.

SELECT SUM(count)
FROM (
    SELECT COUNT(*) AS count
    FROM patient
    GROUP BY age
    HAVING count > 1
) AS x

CodePudding user response：

    select age, count(*) from patient group by age;