CODEWITHSUNDEEP
mysqlsql
Machine
Machine

Reputation: 31

How to join 2 query results together?

Scenario: I have 2 query form 1 table, just want to view both query results as a single query result.

Details: Table: loantrans

+-----+----------+---------+---------+---------+
| tid |   date   | account | purpose |    out  |
+-----+----------+---------+---------+---------+
|  1  |2014-08-12|   975   |  Loan   |   5000  |
|  2  |2014-08-12|   975   |Interest |   850   |
|  3  |2014-08-12|   975   |  Loan   |   150   |
|  4  |2014-08-12|   975   |Interest |   5000  |
+-----+----------+---------+---------+---------+

Query 1:

SELECT MONTH(`loantrans`.`date`) as month, SUM(`loantrans`.`out`) AS loanout
            FROM loantrans
            WHERE  (`loantrans`.`date` BETWEEN '2014-01-01' AND '2014-09-20')
            AND (`loantrans`.`purpose` = 'Loan')
            GROUP BY MONTH(`loantrans`.`date`)
            ORDER BY `loantrans`.`date`

Result:

+-------+---------+
| month | loanout |
+-------+---------+
|   1   |  28000  |
|   2   |  27000  |
|   3   |  10200  |
|   4   |  7000   |
|   5   |  95000  |
|   6   |  2000   |
+-------+---------+

Query 2:

SELECT MONTH(`loantrans`.`date`) as month, SUM(`loantrans`.`out`) AS intout
            FROM loantrans
            WHERE  (`loantrans`.`date` BETWEEN '2014-01-01' AND '2014-09-20')
            AND (`loantrans`.`purpose` = 'Interest')
            GROUP BY MONTH(`loantrans`.`date`)
            ORDER BY `loantrans`.`date`

Result :

+-------+---------+
| month |  intout |
+-------+---------+
|   1   |  2000   |
|   2   |  750    |
|   3   |  200    |
|   4   |  180    |
|   5   |  570    |
|   6   |  625    |
+-------+---------+

What I want is like

+-------+---------+---------+
| month |  intout | loanout |
+-------+---------+---------+
|   1   |  2000   |  28000  |
|   2   |  750    |  27000  |
|   3   |  200    |  10200  |
|   4   |  180    |  7000   |
|   5   |  570    |  95000  |
|   6   |  625    |  2000   |
+-------+---------+---------+

How to get result like this???

Please check this screenshot for more clear idea of what i'm looking for https://www.dropbox.com/s/dpptqb7y4c6xzi5/Capture3.PNG?dl=0 Database: https://www.dropbox.com/s/8gbgrgvil915efr/bankdb.sql_7.zip?dl=0

Upvotes: 0

Views: 88

Answers (5)

Karim Daraf
Karim Daraf

Reputation: 226

Use this with If statment

 SELECT MONTH(l.date) month, 
     SUM(if (l.purpose='Interest' ,l.out , 0) ) intout,
     SUM(if (l.purpose='Loan', l.out ,0 )) loanout
 FROM loantrans l
 WHERE l.date BETWEEN '2014-01-01' AND '2014-09-20'
GROUP BY month
ORDER BY month

Upvotes: 0

Arth
Arth

Reputation: 13110

  SELECT MONTH(lt.date) month, 
         SUM((lt.purpose='Interest')*lt.out) intout,
         SUM((lt.purpose='Loan')*lt.out) loanout
    FROM loantrans lt
   WHERE lt.date BETWEEN '2014-01-01' AND '2014-09-20'
  /* AND lt.purpose IN ('Interest', 'Loan') /* If you have more purposes. */  
GROUP BY month
ORDER BY month

Upvotes: 2

Horaciux
Horaciux

Reputation: 6477

 SELECT MONTH(lt.date) month, 
         SUM( case when lt.purpose='Interest' then lt.out else 0 end) intout,
         SUM(case when lt.purpose='Loan' then lt.out else 0 end) loanout
    FROM loantrans lt
   WHERE lt.date BETWEEN '2014-01-01' AND '2014-09-20'
GROUP BY month
ORDER BY month

http://sqlfiddle.com/#!2/fa8ac/5

Upvotes: 1

Zereges
Zereges

Reputation: 5209

SELECT l1.month, l1.loanout, l2.intout FROM (
    SELECT MONTH(`loantrans`.`date`) as month, SUM(`loantrans`.`out`) AS loanout
        FROM loantrans
        WHERE  (`loantrans`.`date` BETWEEN '2014-01-01' AND '2014-09-20')
        AND (`loantrans`.`purpose` = 'Loan')
        GROUP BY MONTH(`loantrans`.`date`)
        ORDER BY `loantrans`.`date`
   ) AS l1 JOIN (
    SELECT MONTH(`loantrans`.`date`) as month, SUM(`loantrans`.`out`) AS intout
        FROM loantrans
        WHERE  (`loantrans`.`date` BETWEEN '2014-01-01' AND '2014-09-20')
        AND (`loantrans`.`purpose` = 'Interest')
        GROUP BY MONTH(`loantrans`.`date`)
        ORDER BY `loantrans`.`date`
   ) AS l2 ON l1.month = l2.month;

Upvotes: 0

Kritner
Kritner

Reputation: 13765

Probably better ways to do it, but i think this works:

select  coalesce(tblOne.month, tblTwo.month), intOut, loanOut
from    (
        SELECT MONTH(`loantrans`.`date`) as month, SUM(`loantrans`.`out`) AS loanout
        FROM loantrans
        WHERE  (`loantrans`.`date` BETWEEN '2014-01-01' AND '2014-09-20')
        AND (`loantrans`.`purpose` = 'Loan')
        GROUP BY MONTH(`loantrans`.`date`)
) tblOne
left join (
        SELECT MONTH(`loantrans`.`date`) as month, SUM(`loantrans`.`out`) AS intout
        FROM loantrans
        WHERE  (`loantrans`.`date` BETWEEN '2014-01-01' AND '2014-09-20')
        AND (`loantrans`.`purpose` = 'Interest')
        GROUP BY MONTH(`loantrans`.`date`)
) tblTwo on tblOne.month = tblTwo.month
order by month

Upvotes: 0

Related Questions