Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to sum the results of two different queries on different columns?

Here my customers table:

id name
1 abc
2 bcd
3 cde
4 def

and the shifts table:

id id1 id2
1 1
2 2 1
3 1 3

my expected output is:

name num
abc 3
bcd 1
cde 1
def 0

So I just want to count the times the customers.id is in the shifts table, no matter if in id1 or id2. Either of both can be null.

I’m able to show the table for a single column, example:

SELECT t1.name, COUNT(t2.id1) AS num
FROM customers t1
INNER JOIN shifts t2
ON t2.id1=t1.id
WHERE t2.id1=t1.id
GROUP BY t2.id1

and of course I can do the same for id2.
But how I can sum both results?

I’m aware I can use the + operator, like:

COUNT(t2.id1)+COUNT(t2.id2)

but the actual calculation is quite different, since it should change also the JOIN and GROUP clause.

So I tried to sum the results of the whole queries, as suggested here:

SELECT q1.name, q1.num+q2.num
FROM 
    (SELECT t1.name, COUNT(t2.id1) AS num
    FROM customers t1
    INNER JOIN shifts t2
    ON t2.id1=t1.id
    WHERE t2.id1=t1.id
    GROUP BY t2.id1) AS q1,

    (SELECT t1.name, COUNT(t2.id2) AS num
    FROM customers t1
    INNER JOIN shifts t2
    ON t2.id2=t1.id
    WHERE t2.id2=t1.id
    GROUP BY t2.id2) AS q2
WHERE q1.name=q2.name

but it returns nothing since the WHERE clause requires both queries to have the same name and this is not possible. If I remove the WHERE clause the results are wrong since I discard the name of the second query.

How to sum the results of two different queries on different columns?

Tags:

2

Answers


  1. 0

    Use both id1 and id2 in your ON condition.

    SELECT t1.name, COUNT(t2.id) AS num
FROM customers t1
LEFT JOIN shifts t2
ON t1.id IN (t2.id1, t2.id2)
GROUP BY t1.name

    Use LEFT JOIN and COUNT(t2.shift) so you’ll get zero counts for customers that aren’t in any shift.

    This solution assumes that you don’t have id1 = id2, or if you do they should not each contribute to the count.

    You should also consider normalizing the shifts table, to something like

    • id
    • shift_number
    • customer_id
    Login or Signup to reply.
  2. 0

    If shifts.id is unique, then you can do two left joins and use count distinct as @ysth suggested.

    SELECT
  t1.name,
  COUNT(DISTINCT t2.id) + COUNT(DISTINCT t3.id) AS num
FROM customers t1
LEFT JOIN shifts t2 ON t2.id1 = t1.id
LEFT JOIN shifts t3 ON t3.id2 = t1.id
GROUP BY t1.name

    Alternatively, you can use two subqueries:

    WITH shift1 AS (
  SELECT
    t1.name,
    COUNT(t2.id1) AS num
  FROM customers t1
  LEFT JOIN shifts t2 ON t2.id1 = t1.id
  GROUP BY t1.name
),

shift2 AS (
  SELECT
    t1.name,
    COUNT(t3.id1) AS num
  FROM customers t1
  LEFT JOIN shifts t3 ON t3.id2 = t1.id
  GROUP BY t1.name
),

shift_both AS (
  SELECT
    name,
    num
  FROM shift1

  UNION ALL

  SELECT
    name,
    num
  FROM shift2
)

SELECT
  name,
  SUM(num) AS num
FROM shift_both
GROUP BY name
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search