CODEWITHSUNDEEP
mysql
phz
phz

Reputation: 4213

MySQL how to add/subtract two columns together based on date?

I have two select statements that take values from two different tables. the select statements look something like this

SELECT year(timestamp) y, month(timestamp) m, count(id) c
FROM table
WHERE clause="foo"
GROUP BY year(timestamp), month(timestamp)

which returns something like

|-y--|-m|c|
|2013|01|9|
|2013|02|9|
|2013|03|9|
|2013|04|9|

.

SELECT year(timestamp) y, month(timestamp) m, count(id) c
FROM table2
WHERE clause="foo"
GROUP BY year(timestamp), month(timestamp)

which returns something like

|-y--|-m|c|
|2013|01|1|
|2013|03|1|
|2013|04|1|

What I'm looking for is joining the two tables based on the timestamp, and subtract the second table from the first. So it should look like:

|-y--|-m|c|
|2013|01|8|
|2013|02|9|
|2013|03|8|
|2013|04|8|

thanks!

Upvotes: 0

Views: 1489

Answers (2)

sgeddes
sgeddes

Reputation: 62841

Assuming TableA has your master records, then something like this should work:

SELECT t.y, t.m, t.c - IFNULL(t2.c,0) c
FROM
(
    SELECT year(timestamp) y, month(timestamp) m, count(id) c
    FROM table
    WHERE clause="foo"
    GROUP BY year(timestamp), month(timestamp)
) t
LEFT JOIN (
    SELECT year(timestamp) y, month(timestamp) m, count(id) c
    FROM table2
    WHERE clause="foo"
    GROUP BY year(timestamp), month(timestamp)
) t2 ON t.y = t2.y AND t.m = t2.m
UNION 
SELECT t.y, t.m, (t.c * -1) c
FROM
(
    SELECT year(timestamp) y, month(timestamp) m, count(id) c
    FROM table2
    WHERE clause="foo"
    GROUP BY year(timestamp), month(timestamp)
) t
LEFT JOIN (
    SELECT year(timestamp) y, month(timestamp) m, count(id) c
    FROM table
    WHERE clause="foo"
    GROUP BY year(timestamp), month(timestamp)
) t2 ON t.y = t2.y AND t.m = t2.m
WHERE t2.y IS NULL
ORDER BY y, m

I've updated the answer. The first part of the union returns all records in table with or without those matching in table2, subtracting the table2 count when available. The second part of the union returns all records in table2 that do not exist in table multiplying the count column by -1.

There may be a simpler approach, but without really understanding the tables, this is the best I can offer. And unfortunately MySQL doesn't support FULL OUTER JOINs which would alleviate the need for the UNION.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562368

You can use derived tables:

SELECT t1.y, t2.m, t1.c - t2.c AS c
FROM 
  (SELECT year(timestamp) y, month(timestamp) m, count(id) c
   FROM table
   WHERE clause='foo'
   GROUP BY year(timestamp), month(timestamp)) AS t1
LEFT OUTER JOIN
  (SELECT year(timestamp) y, month(timestamp) m, count(id) c
   FROM table2
   WHERE clause='foo'
   GROUP BY year(timestamp), month(timestamp)) AS t2
ON (t1.y, t1.m) = (t2.y, t2.m)

Another solution would be to join the tables and count the distinct rows from each. Assuming id is a primary key column in each table, this could work:

SELECT YEAR(table.timestamp) AS y, MONTH(table.timestamp) AS m,
 COUNT(DISTINCT table.id) - COUNT(DISTINCT table2.id) AS c
FROM table
LEFT OUTER JOIN table2 ON table2.clause = 'foo' AND
  EXTRACT(YEAR_MONTH FROM table.timestamp) =
  EXTRACT(YEAR_MONTH FROM table2.timestamp)
WHERE table.clause='foo'
GROUP BY EXTRACT(YEAR_MONTH FROM table.timestamp)

Upvotes: 0

Related Questions