mysql count table rows with count child tables rows

Question

I have three tables. a>b>c tables. I want to totals separately each other and optimize query. My query is not giving the true counts.

TABLE: a

a
-----------
id
no
create_time

TABLE: b

b
-----------
id
no

TABLE: c

c
-----------
id
b_id

this query is not giving the true counts

SELECT
    DATE( a.create_time ) AS date,
    COUNT( a.id ) AS total_a,
    COUNT( b.id ) AS total_b,
    COUNT( c.id ) AS total_c 
FROM
    `a`
    LEFT JOIN `b` ON `a`.`no` = `b`.`no`
    LEFT JOIN `c` ON `b`.`id` = `c`.`b_id` 
WHERE
    ( `a`.`status` = 1 ) 
GROUP BY
    DATE( a.create_time ) 
ORDER BY
    `date` DESC 
    LIMIT 20

Add Sample data and expected output

Jay Shankar Gupta
– Jay Shankar Gupta

2018-03-27 11:42:35 +00:00
Commented Mar 27, 2018 at 11:42 — Jay Shankar Gupta
– Jay Shankar Gupta, Commented Mar 27, 2018 at 11:42
Try correlated sub-queries in the select list.

jarlh
– jarlh

2018-03-27 11:45:28 +00:00
Commented Mar 27, 2018 at 11:45 — jarlh
– jarlh, Commented Mar 27, 2018 at 11:45
Thanks your answers, I solved with that Andomer's answer :)

Serhat
– Serhat

2018-05-14 06:09:48 +00:00
Commented May 14, 2018 at 6:09 — Serhat
– Serhat, Commented May 14, 2018 at 6:09

Andomar · Accepted Answer · 2018-03-27 11:48:10Z

3

A left join repeats each row on the left hand side for each matching row on the right hand side. So you get more rows than in the original.

An easy fix is to count just the unique identifiers:

SELECT
    DATE( a.create_time ) AS date,
    COUNT( DISTINCT a.id ) AS total_a,
    COUNT( DISTINCT b.id ) AS total_b,
    COUNT( DISTINCT c.id ) AS total_c

edited Mar 27, 2018 at 11:48

answered Mar 27, 2018 at 11:42

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

Collectives™ on Stack Overflow

mysql count table rows with count child tables rows

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