MySQL Query - SUM of COUNT from multiple tables

Question

I have three tables:

customers: id, name
contracts_jewels: id, customer_id, paid, transferred, final_date
contracts_objects: id, customer_id, paid, transferred, final_date

As you see, the structure of the last two tables is the same. The "paid" and the "transferred" fields contain the value 0 or 1.

What I need is to make a query which should return all the clients (no matter if they have contracts or not), and for each client: id, name, count_contracts_all, count_contracts_active

where:

count_contracts_all would mean the sum of [SELECT COUNT( * ) FROM contracts_jewels WHERE customer_id=3 (for example)] and [SELECT COUNT( * ) FROM contracts_objects WHERE customer_id=3 (for example)]
count_contracts_active would mean the sum of [SELECT COUNT( * ) FROM contracts_jewels WHERE customer_id=3 AND final_date>=Now() AND paid=0 AND transferred=0] and [SELECT COUNT( * ) FROM contracts_objects WHERE customer_id=3 AND final_date>=Now() AND paid=0 AND transferred=0]

Any idea? Would you please help me? Thank you!

I think a UNION can help you: SELECT field_1[, field_2,…] FROM table_1[, table_2,…] UNION [ALL] SELECT field_a[, field_b,...] FROM table_a[, table_b,…]; — Holger
– Holger, Commented Apr 12, 2016 at 14:11

Vatev · Accepted Answer · 2016-04-12 14:15:02Z

1

You can count the contracts separately and then just join them up to the customers:

SELECT
    c.id,
    COALESCE(oc.active_count,0) + COALESCE(jc.active_count,0) as count_contracts_active,
    COALESCE(oc.total_count,0) + COALESCE(jc.total_count,0) as count_contracts_all
FROM customers c
LEFT JOIN (
    SELECT
        customer_id
        COUNT(*) as total_count,
        COUNT(IF(final_date>=Now() AND paid=0 AND transferred=0,1,NULL)) as active_count
    FROM contracts_jewels
    GROUP BY customer_id
) as oc ON oc.customer_id = c.id
LEFT JOIN (
    SELECT
        customer_id
        COUNT(*) as total_count,
        COUNT(IF(final_date>=Now() AND paid=0 AND transferred=0,1,NULL)) as active_count
    FROM contracts_objects
    GROUP BY customer_id
) as jc ON jc.customer_id = c.id

answered Apr 12, 2016 at 14:15

Vatev

7,5801 gold badge36 silver badges42 bronze badges

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

2 Comments

fortitude23 Over a year ago

It goes like a charm! Thank you! I am afraid i cannot rate your answer, I am a new user here, but I am very grateful!

Preuk Over a year ago

@fortitude23: you can't rate yet, but you can still "accept" the answer if it helped you solving your problem

sphinks · Accepted Answer · 2016-04-12 14:25:14Z

1

One fast solution I can think of right now is:

SELECT COUNT(`temp_table`.*) FROM (
    SELECT * FROM contracts_jewels WHERE customer_id=3 UNION ALL
    SELECT * FROM contracts_objects WHERE customer_id=3) AS `temp_table`

AND

SELECT COUNT(`temp_table`.*) FROM (
SELECT * FROM contracts_jewels WHERE customer_id=3 AND final_date>=Now() AND paid=0 AND transferred=0 UNION ALL
    SELECT * FROM contracts_objects WHERE customer_id=3 AND final_date>=Now() AND paid=0 AND transferred=0)  AS `temp_table`

answered Apr 12, 2016 at 14:25

sphinks

393 bronze badges

Comments

Unix One · Accepted Answer · 2016-04-12 14:56:14Z

You can join each of those tables twice and add their corresponding COUNTs in your result:

SELECT
    c.id,
    (COUNT(cj1.id)+COUNT(co1.id)) AS count_contracts_all,
    (COUNT(cj2.id)+COUNT(co2.id)) AS count_contracts_active
FROM
    customers c
    LEFT OUTER JOIN contracts_jewels cj1 ON c.id = cj1.customer_id
    LEFT OUTER JOIN contracts_objects co1 ON c.id = co1.customer_id
    LEFT OUTER JOIN contracts_jewels cj2 ON
        c.id = cj2.id AND
        cj2.final_date >= NOW() AND
        cj2.paid = 0 AND
        cj2.transferred = 0
    LEFT OUTER JOIN contracts_object co2 ON
        c.id = co2.id AND
        co2.final_date >= NOW() AND
        co2.paid = 0 AND
        co2.transferred = 0
GROUP BY c.id

Note: I haven't run this, but hopefully it sets you in the right direction.

Sayed Abolfazl Fatemi · Accepted Answer · 2020-10-30 12:29:46Z

0

simple solution:

SELECT SUM(c) FROM (
    SELECT COUNT(1) as c FROM `tbl1` where ...
UNION
    SELECT COUNT(1) as c FROM tbl2 where ...
UNION
    SELECT COUNT(1) as c FROM tbl3 where ...
) al

answered Oct 30, 2020 at 12:29

Sayed Abolfazl Fatemi

3,9313 gold badges40 silver badges51 bronze badges

Collectives™ on Stack Overflow

MySQL Query - SUM of COUNT from multiple tables

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related