How to selecting data from two queries using union?

Question

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 ?

How to selecting data from two queries using union?

0 Answers0