CODEWITHSUNDEEP
phpmysql
Shri Gowtham
Shri Gowtham

Reputation: 39

Union query to fetch SUM values

Totally 12 db tables(some tables repeated). I have to fetch SUM(values) from each table of particular date.I have used UNION query,But it returns the value of first table used in query. Remaining table returned nothing.Can Anybody help me.Here my code.

$sel = mysql_query("
SELECT 
    SUM(collection_amount) AS cash_total 
FROM 
    collection_entry 
WHERE 
    date='$entered_date' 
    AND collection_type='DC' 
UNION 
SELECT 
    SUM(amt) AS cheque_redeposit_total 
FROM 
    cheque_redeposit 
WHERE 
    redeposited_on1 
    OR redeposited_on2='$entered_date' 
UNION 
SELECT 
    SUM(collection_amount) AS not_cleared_total 
FROM 
    collection_entry 
WHERE 
    cheque_status='not cleared' 
    AND date='$entered_date' 
UNION 
SELECT 
    SUM(collection_amt) AS route_collection_total 
FROM 
    route_collection 
WHERE 
    entered_date='$entered_date' 
UNION 
SELECT 
    SUM(amt) AS return_total 
FROM 
    cheque_return 
WHERE 
    return_date1 OR return_date2 OR return_date3='$entered_date' 
UNION 
SELECT 
    SUM(collection_amount) AS cheque_total 
FROM 
    collection_entry 
WHERE 
    collection_type='CC' 
    AND date='$entered_date' 
UNION 
SELECT 
    SUM(debit2) AS voucher_receipt_total 
FROM 
    voucher_posting 
WHERE 
    receipt_type='R' 
    AND date='$entered_date' 
UNION 
SELECT 
    SUM(credit2) AS voucher_payment_total 
FROM 
    voucher_posting 
WHERE 
    receipt_type='P' 
    AND date='$entered_date' 
UNION 
SELECT 
    SUM(amt) AS others_total 
FROM 
    others_remittance 
WHERE 
    entered_date='$entered_date' 
UNION 
SELECT 
    SUM(amt) AS short_total 
FROM 
    short_remittance 
WHERE 
    entered_date='$entered_date' 
UNION 
SELECT 
    SUM(amount) AS more_paid 
FROM 
    difference 
WHERE 
    entered_date='$entered_date' 
    and paid_type='more' 
UNION 
SELECT 
    SUM(amount) AS unpaid 
FROM 
    difference 
WHERE 
    entered_date='$entered_date' 
    and paid_type='unpaid'");
 while($row=mysql_fetch_array($sel)) 
 {
  $cash_total=$row['cash_total'];
  $cheque_redeposit_total=$row['cheque_redeposit_total'];
  $not_cleared_total=$row['not_cleared_total'];
  $route_collection_total=$row['route_collection_total'];
  $return_total=$row['return_total'];
  $cheque_total=$row['cheque_total'];
  $voucher_receipt_total=$row['voucher_receipt_total'];
  $voucher_payment_total=$row['voucher_payment_total'];
  $others_total=$row['others_total'];
  $short_total=$row['short_total'];
  $more_paid=$row['more_paid'];
  $unpaid=$row['unpaid'];
  $net_total = (($cash_total + $route_collection_total) - $return_total);
 }

Upvotes: 0

Views: 293

Answers (1)

Sirko
Sirko

Reputation: 74046

UNION just appends rows to one another. So in your case you just get a list of your sums in a row-wise fashion.

  • [value for cash_total]
  • [value for cheque_redeposit_total]
  • [value for not_cleared_total]
  • ...

If you really have to get all the data in one row, you can use something like this:

SELECT * FROM
(SELECT SUM(collection_amount) AS cash_total FROM collection_entry WHERE date='$entered_date' AND collection_type='DC') as t1,
(SELECT SUM(amt) AS cheque_redeposit_total FROM cheque_redeposit WHERE redeposited_on1 OR redeposited_on2='$entered_date') AS t2,
(SELECT SUM(collection_amount) AS not_cleared_total FROM collection_entry WHERE cheque_status='not cleared' AND date='$entered_date') AS t3,
(SELECT SUM(collection_amt) AS route_collection_total FROM route_collection WHERE entered_date='$entered_date') AS t4,
...

Upvotes: 1

Related Questions