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
– Trent, Commented Dec 20, 2015 at 10:41
tnx @AlexJolig for the improved formatting. Quite new to stackoverflow and didn't know how to properly do it. :) — sorinr
– sorinr, Commented Dec 20, 2015 at 10:49
You are right, @Trent. It can be emulated, like in the answers to this question: stackoverflow.com/questions/2384298/… — Renzo
– Renzo, Commented Dec 20, 2015 at 10:49
As you can see from the title, the data comes from same table @Renzo — sorinr
– sorinr, Commented Dec 20, 2015 at 10:53

Thomas Ghesquiere · Accepted Answer · 2015-12-20 13:47:58Z

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, 2015 at 13:47

answered Dec 20, 2015 at 10:45

Thomas Ghesquiere

5024 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sorinr Over a year ago

in my case 'somefilter' has the same value in both SQL queries @Thomas Ghesquiere

sorinr Over a year ago

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

Trent Over a year ago

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 · Accepted Answer · 2015-12-20 10:49:27Z

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, 2015 at 10:49

answered Dec 20, 2015 at 10:21

Trent

3,1332 gold badges37 silver badges52 bronze badges

1 Comment

sorinr Over a year ago

with some little changes in the WHERE clause this solution brings the results I need. Tnx @Trent

David · Accepted Answer · 2015-12-20 13:49:35Z

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, 2015 at 13:49

answered Dec 20, 2015 at 10:34

David

5323 silver badges7 bronze badges

3 Comments

Trent Over a year ago

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.

David Over a year ago

i think in mssql this would fail, in but in mysql this should work just fine

sorinr Over a year ago

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

Collectives™ on Stack Overflow

how to mysql join two select query results from same table

3 Answers 3

3 Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related