getting count on same column using inner join

Question

I have a table like this, in which I need to set the male and female counts for the primary key id: summaryTable:

    id   femaleCount   maleCount
    -----------------------------
    s1      ?            ?
    s2      ?            ?

... and so on

There is a detail table as below, that has the users corresponding to each id of summaryTable:

    id    parentId   userId
    --------------------------
    1     s1         u1
    2     s1         u2
    3     s2         u2
    4     s2         u2

...and so on

The third is the user table like this: userTable:

userId   gender
-------------
u1       M
u2       F
u3       F
..and so on

I have to update the summary table with the counts of male and female. So as per the above, for id=s1, femaleCount should be set to 1 , maleCOunt=1 For id=s2, femaleCOunt should get set to 2 and maleCount=0

Is this possible to do using an UPDATE query in MySQL?

I tried the following, but this returns the sum of occurences of a user i.e. if u1 occurs 2 times for p1(say), then it will return count as 2 and not 1:

SELECT 
  d.parentId,
  SUM(gender = 'F') AS 'F#', 
  sum(gender = 'M') as 'M#'
FROM detailsTable as d 
JOIN userTable as c on c.userId = d.userId
GROUP BY d.parentId;

Also tried as below, but it gave an error:

select d.parentId, 
    count(case when c.gender='M' then 1 end) as male_cnt,
    count(case when c.gender='F' then 1 end) as female_cnt,
 from detailsTable d, userTable c where d.userId=c.userId group by d.parentId, d.userId ;

Further, my problem doesnt just end at the select, I need to get the values and then update these in the summary table too.

TonyWaddle · Accepted Answer · 2016-02-27 19:39:11Z

1

I might be rusty on the syntax for MySql but I believe this does what you need. The CASE/SUM is effectively a pivot to get the counts, then you can update the table as normal.

UPDATE  summaryTable AS st
        INNER JOIN ( SELECT parentId
                           ,SUM(CASE WHEN gender = 'f' THEN 1
                                     ELSE 0
                                END) femaleCount
                           ,SUM(CASE WHEN gender = 'm' THEN 1
                                     ELSE 0
                                END) maleCount
                     FROM   userTable d
                            INNER JOIN (SELECT DISTINCT parentId, userId FROM detail) ut ON d.userId = ut.userId
                     GROUP BY parentId
                   ) AS c ON c.parentId = st.parentId
SET     femaleCount = c.femaleCount
       ,maleCount = c.maleCount

edited Feb 27, 2016 at 19:39

answered Feb 27, 2016 at 19:21

TonyWaddle

1031 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user5917011 Over a year ago

Like I said, a user's gender should not be counted twice, if the user is repeated. The above solution is updating it like that. If the same user is occuring more than once in details table for a given parentId, then the above query counts it as many times

TonyWaddle Over a year ago

Sorry I missed that - added a distinct subquery on the detail table. Does that solve it?

Collectives™ on Stack Overflow

getting count on same column using inner join

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related