mardi 31 juillet 2018

how to join two query result and grouped it by a column

i have query joined by union and it give me desirable result separatly in rows i want to join those two rows and add its values but i dont know how to do it

the query is

SELECT `b`.`main_code`,`b`.`account_title`,SUM(CASE WHEN `a`.`type`='CP' AND `a`.`interactive_person`='6' 
AND `a`.`date` BETWEEN '2018-02-12' AND '2018-07-31' THEN `a`.`balance` ELSE 0 END) AS `credit`, 
SUM(CASE WHEN `a`.`type`='CR' AND `a`.`interactive_person`='6' AND `a`.`date` BETWEEN '2018-02-12' 
AND '2018-07-31' THEN `a`.`balance` ELSE 0 END) AS `debit`, SUM(CASE WHEN `a`.`type`='CR' AND 
`a`.`interactive_person`='6' AND `a`.`date` BETWEEN '2018-02-12' AND '2018-07-31' 
THEN `a`.`balance` WHEN `a`.`type`='CP' THEN -1 * `a`.`balance` ELSE 0 END) AS `balance` 
FROM `vouchers` AS `a`,`data` AS `b` WHERE `a`.`interactive_person`='6' AND `a`.`post_status`='yes' 
AND `a`.`interactive_person`=`b`.`main_code` AND `a`.`date` BETWEEN '2018-02-12' AND '2018-07-31'
UNION 
SELECT `b`.`main_code`,`b`.`account_title`,IFNull(SUM(`a`.`debit`),0) AS `debit`, 
IFNull(SUM(`a`.`credit`),0) AS `credit`, (`debit` - `credit`) AS `balance` FROM `journal_vouchers` AS `a`,
`data` AS `b` WHERE `a`.`account_id`='44' AND `a`.`date` BETWEEN '2018-02-12' AND '2018-07-31'
AND `post_status`='yes' AND `cancel_status`='off' AND `a`.`account_id`=`b`.`account_code` 
GROUP BY `b`.`main_code`;

the query is a bit complex let me explain it little: this query takes the result from 3 tables but main thing is that before union its take result from two table and after union two tables in which one table data interact with both. in short the result i get is

main_code | account_title | credit | debit | balance

6 | cash account | 5200 | 520 | -4680

6 | cash account | 0 | 200 | -200

i want the full query grouped by maincode but i dont know how do it i want result to be

6 |cash account | 5200 | 320 | -4880




Aucun commentaire:

Enregistrer un commentaire