how to mysql join two select query results from same table

Question

I have a mysql table called 'records' with this structure: docdate, db, cr, total

and two queries like:

1. SELECT docdate, SUM(total) as ins FROM records where db='somefilter' AND 'someotherfilters' GROUP BY docdate
2. SELECT docdate, SUM(total) as outs FROM records where cr='somefilter' GROUP BY docdate

What I want to achive in one sql statement is a result like:

docdate         ins      outs
2015-12-15     NULL   1200.00
2015-12-16  1000.00      NULL
2015-12-17  2500.00   2000.00

Any idea will be highly appreciated. Tnx

As far as I know, FULL OUTER JOIN is not supported in MySQL. @Renzo — Trent, Dec 20 '15 at 10:41
tnx @AlexJolig for the improved formatting. Quite new to stackoverflow and didn't know how to properly do it. :) — sorinr, Dec 20 '15 at 10:49
You are right, @Trent. It can be emulated, like in the answers to this question: http://stackoverflow.com/questions/2384298/why-does-mysql-report-a-syntax-error-on-full-outer-join/3357262#3357262 — Renzo, Dec 20 '15 at 10:49
As you can see from the title, the data comes from same table @Renzo — sorinr, Dec 20 '15 at 10:53
@sorinr - Renzo was suggesting you full outer join on itself - this is generally how you would combine the data in such as way. See the full outer would return the results in an IF/ELSE way (as you want) either the sums are IN, or sums are OUT depending on the where criteria. — Trent, Dec 20 '15 at 10:58

Thomas Ghesquiere · Accepted Answer · 2015-12-20T13:47:58.753

1

If you don't mind to have 0 instead of NULL, you can put an IF inside your SUM, like this:

SELECT
    docdate, 
    SUM(IF(db='somefilter' AND 'someotherfilters', total, 0)) as ins,
    SUM(IF(cr='somefilter', total, 0)) as outs
FROM records
GROUP BY docdate

Edit:

If you really want to have NULL values, you could wrap the SUM in a NULLIFstatement

... NULLIF(SUM(...), 0) AS ...

edited Dec 20 '15 at 13:47

answered Dec 20 '15 at 10:45

Thomas Ghesquiere

402
4
7

in my case 'somefilter' has the same value in both SQL queries @Thomas Ghesquiere – sorinr Dec 20 '15 at 11:26
Tested also this solution and it works like a charm. Be careful and just add ')' before 'as ins or as outs' to close the SUM function. Thank you @Thomas Ghesquiere – sorinr Dec 20 '15 at 13:18
word of warning here. NULLIF may hide problems. If the OP really is looking for nulls (meaning absolutely no data for the docdate) then this will mask true 0 sums for a day (where there are records for the docdate but they happen to be zero). – Trent Dec 22 '15 at 23:40

Trent · Answer 2 · 2015-12-20T10:49:27.723

0

I like the SUMIF answer - here is another way to get the same outcome (but with null returns as requested)

Keep in mind, both of these approaches have horrendous performance issues if the records table is large (as both require sequential scans. Here is the SQL fiddle http://sqlfiddle.com/#!9/b066c/1

SELECT
  docdate,
  (
    SELECT SUM(total) FROM records r2 WHERE r2.docdate = records.docdate AND r2.field1 = 'Live'
  ) as ins,
  (
    SELECT SUM(total) FROM records r3 WHERE r3.docdate = records.docdate AND r3.field1 = 'Test' AND r3.field2 = 'OK'
  ) as outs
FROM
  records
GROUP BY 1

edited Dec 20 '15 at 10:49

answered Dec 20 '15 at 10:21

Trent

2,909
1
31
46

with some little changes in the WHERE clause this solution brings the results I need. Tnx @Trent – sorinr Dec 20 '15 at 12:13

David · Answer 3 · 2015-12-20T13:49:35.007

0

One way to do it is you can use use UNION, see http://dev.mysql.com/doc/refman/5.7/en/union.html

SELECT docdate, SUM(ins) as ins , SUM(outs) as outs
FROM 
(
    (
    SELECT docdate, SUM(total) as ins , null as outs
    FROM records 
    where db='somefilter' AND 'someotherfilters' 
    GROUP BY docdate
    )
    UNION
    (
    SELECT docdate, null as ins, SUM(total) as outs 
    FROM records 
    where cr='somefilter' GROUP BY docdate
    )
) AS t
GROUP BY docdate;

edited Dec 20 '15 at 13:49

answered Dec 20 '15 at 10:34

David

522
3
7

I imagine this would fail because you need to group by all non-aggregate columns. In the first SELECT there are two non-aggregates positions 1 and 3, but in the union positions 1 and 2 are the non-aggregates. – Trent Dec 20 '15 at 10:36
i think in mssql this would fail, in but in mysql this should work just fine – David Dec 20 '15 at 10:53
this union actually creates rows from the first sql query and after that adds results from the second querry. I just need to find a way to pair both results by docdate. @Trent – sorinr Dec 20 '15 at 11:13

how to mysql join two select query results from same table

3 Answers3