0

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.

  • group_id = ( SELECT MAX(timestamp) ...) ? Is that correct? – ypercubeᵀᴹ Dec 5 '18 at 15:34
  • how can I subtract query result B from query result A so I can see the difference between those two? Decide what do you want. To see records which present in A and absent in B (substract), or to see records which present in one of this tables and absent in another marking what query the record is from (difference)... – Akina Dec 5 '18 at 15:52
1

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

  • Oh, yes the WHERE statement is wrong. I've changed that afterwards but forgot to change the group_id to timestamp too but I can change your statement for my needs. Thanks anyway, this works! – user6679493 Dec 6 '18 at 7:42
0

I believe what you are asking for can be satisfied using the MINUS operator in MySQL.

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

MINUS

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

Here is a blog that has a decent example.

  • MINUS is an operator only in Oracle. – ypercubeᵀᴹ Dec 5 '18 at 15:43
  • Ah, you are correct. I did not thoroughly read the Blog post to where they said it MINUS is not supported in MySQL. They do make the assertion though that the purpose of a MINUS operator can be accomplished using JOINs - mysqltutorial.org/mysql-join – SQL Sean Dec 5 '18 at 15:46
0

subtract query result B from query result A

WITH q1 AS ('query1 text'),
     q2 AS ('query2 text')
SELECT q1.*
FROM q1 
NATURAL LEFT JOIN q2
WHERE q2.some_not_null_field IS NULL

For old server versions:

SELECT q1.*
FROM ('query1 text') AS q1 
NATURAL LEFT JOIN ('query2 text') AS q2
WHERE q2.some_not_null_field IS NULL

Remember - the field names in both queries must be identical!


In this particular case:

SELECT character_name,ROUND(minutes_played / 60, 0)
FROM outfit_member
WHERE group_id = ( SELECT MAX(`timestamp`) FROM outfit_member ) -- ??? strange condition
  AND COALESCE(`timestamp`, NOW()) >= NOW() - INTERVAL 1 WEEK

If timestamp field is defined as NOT NULL, COALESCE() is excess and can be removed freely.

0

If each query returns a single value, this works fine:

SELECT ( SELECT value FROM ... ) -
       ( SELECT value FROM ... );

Note that the outer SELECT has no FROM.

Your example seems to imply each SELECT returns key-value pairs, and you want to do the diffs after pairing up the keys:

SELECT a.value - b.value
    FROM ( SELECT key, value FROM ... ) AS a
    JOIN ( SELECT key, value FROM ... ) AS b
      ON a.key = b.key;

These subqueries are called "derived" tables. In older versions of MySQL, having two of them could be quite slow. (5.6 mostly solves that by automatically creating an index for one of the subqueries.)

(Another Answer uses CTEs (not WITH), this requires MySQL 8.9.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.