MySQL Multiple Aggregates

Question

Without the third join D.cid = C.id, this query gives me the count of C. With the third join it corrupts the count and gets unwanted tuples into the count of C's join. So how can I get the count of C and D without having the C count effected? Is there a form of parenthesis I can use to make sure I get the correct count?

SELECT A.*, B.*, COUNT(C.aid) AS cCount 
FROM tableA A

LEFT JOIN tableC AS C ON A.id = C.aid
INNER JOIN tableB AS B ON A.id = B.aid
LEFT JOIN tableD AS D ON D.cid = C.id

GROUP BY  A.id

There's COUNT(DISTINCT C.aid), unless you already expected to get duplicate c.aid's before you joined in table D. — Marc B
– Marc B, Commented Nov 15, 2011 at 4:03

DRapp · Accepted Answer · 2011-11-15 04:03:43Z

1

I would have the counts from the other tables pre-aggregated unto themselves and joined... something like...

SELECT 
      A.*, 
      B.*, 
      COALESCE( PreAggC.CCount, 0 ) as CCount,
      COALESCE( PreAggC.WithDCount, 0 ) as WithDCount
   FROM 
      tableA A

         JOIN tableB B
            on A.ID = B.aID

         LEFT JOIN ( select aID, 
                            count( distinct id ) CCount,
                            count(*) as WithDCount
                        from tableC
                           left join tableD D
                              on c.ID = D.cID
                        group by aID ) PreAggC
            on A.id = PreAggC.aID

Now, do you really want how many entries actually have "D" records? so I included both counts... distinct "C" entries, and the overall count with correlation with "D"

answered Nov 15, 2011 at 4:03

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

MySQL Multiple Aggregates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related