How to combine 2 select query in one table?-CodePudding

SELECT DATE(date), SUM(amount) AS `Bank Transfer`
FROM partial_payment
WHERE pay_code = "BT" 
GROUP BY DATE(date)

SELECT DATE(date), SUM(amount) AS Cash
FROM partial_payment
WHERE pay_code = "AC" 
GROUP BY DATE(date)

How to combine these 2 SELECT statement so that i can have a table with 3 columns namely DATE(date), Bank Transfer and Cash ?

CodePudding user response：

you can try this ?

select aa.date, Bank_Transfer, Cash from (
SELECT DATE(date) as date, SUM(amount) AS `Bank_Transfer`
FROM partial_payment
WHERE pay_code = "BT" 
GROUP BY DATE(date))aa, (

SELECT DATE(date) as date, SUM(amount) AS Cash
FROM partial_payment
WHERE pay_code = "AC" 
GROUP BY DATE(date)) bb where aa.date=bb.date

CodePudding user response：

you can try this one ?

select aa.date, Bank_Transfer, Cash from (
SELECT DATE(date) as date, SUM(amount) AS `Bank_Transfer`
FROM partial_payment
WHERE pay_code = "BT" 
GROUP BY DATE(date))aa
full join (SELECT DATE(date) as date, SUM(amount) AS Cash
FROM partial_payment
WHERE pay_code = "AC" 
GROUP BY DATE(date)) bb on aa.date=bb.date