CODEWITHSUNDEEP
sqlselectjoincount
user8171484
user8171484

Reputation:

SQL count on a joined table

I've got 2 tables, 1 is Persons and 1 is Relationships.

I want to retrieve the person with the most children.

Currently I'm joining the tables like this:

SELECT 
   PERSONS.ID, 
   PERSONS.NATIVE_COUNTRY, 
   RELATIONSHIPS.PERSON_ID, 
   RELATIONSHIPS.RELATION 
FROM PERSONS
INNER JOIN RELATIONSHIPS ON RELATIONSHIPS.PERSON_ID = ID 
                            AND RELATIONSHIPS.RELATION = 'child';

Now I'm trying to count PERSONS.ID and get the max, but not sure how to do that.

Upvotes: 0

Views: 417

Answers (2)

ihm017
ihm017

Reputation: 182

you can try using group by:

SELECT PERSONS.ID, COUNT(*) "No of Children" FROM PERSONS INNER JOIN RELATIONSHIPS ON RELATIONSHIPS.PERSON_ID = ID AND RELATIONSHIPS.RELATION = 'child' GROUP BY persons.id;

Upvotes: 0

Veljko89
Veljko89

Reputation: 1953

You could use partition by ...

SELECT PERSONS.ID, PERSONS.NATIVE_COUNTRY, RELATIONSHIPS.PERSON_ID, 
       RELATIONSHIPS.RELATION,
       count(*) over (partition by Persons.ID order by Persons.ID)
FROM PERSONS
INNER JOIN RELATIONSHIPS ON RELATIONSHIPS.PERSON_ID = ID AND 
                            RELATIONSHIPS.RELATION = 'child';

Also, you could do simple GROUP BY if you don't like partition by 

SELECT PERSONS.ID, PERSONS.NATIVE_COUNTRY, RELATIONSHIPS.PERSON_ID, 
       RELATIONSHIPS.RELATION,
       COUNT(*)
FROM PERSONS
INNER JOIN RELATIONSHIPS ON RELATIONSHIPS.PERSON_ID = ID AND 
                            RELATIONSHIPS.RELATION = 'child';
GROUP BY  PERSONS.ID, PERSONS.NATIVE_COUNTRY, RELATIONSHIPS.PERSON_ID,
          RELATIONSHIPS.RELATION

Also, when using joins try to use alias, it's better for readability and no reason for all upper case characters

Upvotes: 3

Related Questions