CODEWITHSUNDEEP
mysqlsqlinner-join
user5917011
user5917011

Reputation: 1137

getting count on same column using inner join

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.

Upvotes: 0

Views: 290

Answers (1)

TonyWaddle
TonyWaddle

Reputation: 103

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

Upvotes: 1

Related Questions