How to selecting data from two queries using union?

0 votes
0 answers
95 views
                          I have three tables. I am trying to taking the data with two times interval after then subtracting from first two second for every possible row and getting the ledger report.

Solution is given by: **jkavalik**  
Solved MySql Fiddle  

My Problem
MYSQL FIDDLE 
 
My simple query run and gives me some data:

      SELECT lh.group_id,lh.opening_balance,lh.ledger_head_id,sum(dc.debit)
        AS sum_dr,sum(dc.credit) AS sum_cr FROM ac_debit_credit dc 
		INNER JOIN ac_transaction_master tm ON tm.tran_id=dc.tran_id 
		INNER JOIN ac_ledger_head lh ON lh.ledger_head_id=dc.ledger_head_id
		WHERE (tm.transaction_date BETWEEN '2015/09/01' AND '2015/10/10') AND lh.group_id IN('8', '9', '56', '10', '11', '12', '13',
        '14') group by dc.ledger_head_id
    UNION
       SELECT lh.group_id,lh.opening_balance,lh.ledger_head_id,sum(dc.debit) AS sum_dr2,sum(dc.credit) AS sum_cr2 FROM ac_debit_credit dc 
		INNER JOIN ac_transaction_master tm ON tm.tran_id=dc.tran_id 
		INNER JOIN ac_ledger_head lh ON lh.ledger_head_id=dc.ledger_head_id
			WHERE (tm.transaction_date<'2015/09/01') AND lh.group_id IN('8', '9', '56', '10', '11', '12', '13',
        '14') group by dc.ledger_head_id  


But I need to execute this:
      
    SELECT PF.opening_balance,PF.sum_dr,PF.sum_cr,PF.sum_dr2,PF.sum_cr2,(PF.sum_dr-PF.sum_cr) as cv,(PF.sum_dr2-PF.sum_cr2) as cv2 FROM (
    SELECT PR.* FROM(  
	SELECT lh.group_id,lh.opening_balance,lh.ledger_head_id,sum(dc.debit) AS sum_dr,sum(dc.credit) AS sum_cr FROM ac_debit_credit dc 
		INNER JOIN ac_transaction_master tm ON tm.tran_id=dc.tran_id 
		INNER JOIN ac_ledger_head lh ON lh.ledger_head_id=dc.ledger_head_id
			WHERE (tm.transaction_date BETWEEN '2015/09/01' AND '2015/10/10') AND lh.group_id IN('8', '9', '56', '10', '11', '12', '13',
        '14') group by dc.ledger_head_id
        ) AS PR
     UNION
       SELECT PV.* FROM(  
       SELECT lh.group_id,lh.opening_balance,lh.ledger_head_id,sum(dc.debit) 
       AS sum_dr2,sum(dc.credit) AS sum_cr2 FROM ac_debit_credit dc 
		INNER JOIN ac_transaction_master tm ON tm.tran_id=dc.tran_id 
		INNER JOIN ac_ledger_head lh ON lh.ledger_head_id=dc.ledger_head_id
		WHERE (tm.transaction_date<'2015/09/01') AND lh.group_id IN('8', '9', '56', '10', '11', '12', '13',
        '14') group by dc.ledger_head_id
      ) AS PV GROUP BY ledger_head_id 
	  ) AS PF;

Where am I wrong in my second query ?

                        
Asked by Kabir Hossain (103 rep)
Oct 12, 2015, 05:43 PM
Last activity: Oct 13, 2015, 05:51 AM
How to selecting data from two queries using union?

Related Questions
