Mysql – Subtract from two MYSQL Queries

MySQLquery

How can I subtract query result B from query result A so that I can see the difference between the two?

A: 

SELECT
  character_name,
  ROUND(minutes_played / 60, 0)
FROM
  outfit_member
WHERE timestamp =
(
  SELECT
    MAX(timestamp)
  FROM
    outfit_member
)

B: 

SELECT
  character_name,
  ROUND(minutes_played / 60, 0)
FROM
  outfit_member
WHERE
  timestamp < NOW() - INTERVAL 1 WEEK

What I want is the Result of ROUND(minutes_played / 60, 0) in B minus ROUND(minutes_played / 60, 0) in A. For example if B returns 1000 Hours and A returns 900 I get 100 Hours.

Best Answer

I assume all "characters" in result of second query exists in result from first query:

SELECT a.character_name
     , SUM(ROUND(a.minutes_played / 60, 0)) 
     - SUM(COALESCE(ROUND(b.minutes_played / 60, 0), 0))
FROM outfit_member a
LEFT JOIN outfit_member b
     ON a.character_name = b.character_name 
WHERE a.timestamp =  (
      SELECT MAX(c.timestamp)
      FROM outfit_member c
)
AND b.timestamp < NOW() - INTERVAL 1 WEEK
GROUP BY a.character_name;

I added a sum since I assume that each character has several rows.

EDIT: since this was an accepted answer I won't change it but suspect that it may give the wrong answer under certain conditions. Anyhow, another option is to add the conditions to the aggregate functions like:

SELECT a.character_name
     , SUM(CASE WHEN a.timestamp =  (SELECT MAX(c.timestamp)
                                     FROM outfit_member c)
           THEN ROUND(a.minutes_played / 60, 0)
           ELSE 0
           END) 
     - SUM(CASE WHEN a.timestamp < NOW() - INTERVAL 1 WEEK
           THEN ROUND(a.minutes_played / 60, 0)
           ELSE 0
           END)
FROM outfit_member a
GROUP BY a.character_name;

The latter query potentially scans more rows, but avoid a join

Related Question