CODEWITHSUNDEEP
mysql
smk3108
smk3108

Reputation: 131

how to combine two mysql query result?

QUERY 1:

SELECT SUM(total) AS total, company, id 
FROM
(SELECT  invoicereferencenumber AS ref, invoiceamount AS total, companyname AS company, customer.customerid AS id FROM customer, rental_invoice AS r WHERE customer.customerid=r.customerid
UNION
SELECT  invoicereferencenumber AS ref, invoiceamount AS total, companyname AS company, customer.customerid AS id FROM customer, sale_invoice AS s WHERE customer.customerid=s.customerid
UNION
SELECT  invoicereferencenumber AS ref, invoiceamount AS total, companyname AS company, customer.customerid AS id FROM customer, service_invoice AS se WHERE customer.customerid=se.customerid)
AS tmptotal
GROUP BY id

QUERY 2:

SELECT SUM(paid) AS paid, company, cid 
FROM
(SELECT cash_amount AS paid, companyname AS company, customerid AS cid FROM payment_cash AS p, customer AS c WHERE cash_cus_id=customerid
UNION
SELECT cheque_value AS paid, companyname AS company, customerid AS cid FROM payment_cheque AS p, customer AS c WHERE cheque_cus_id=customerid
UNION
SELECT rtgs_value AS pain, companyname AS company, customerid AS cid FROM payment_rtgs AS p, customer AS c WHERE rtgs_cus_id=customerid)
AS tmppaid
GROUP BY cid

I have to combine these result as

----------------------------------------
|  total  |   Paid   |  company  | id  |
----------------------------------------

Please help me out. Thanks in advance

Upvotes: 0

Views: 91

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166336

Well I would start by trying something like

SELECT  TotalQuery.*,
        PaidQuery.paid
FROM    (
            SELECT SUM(total) AS total, company, id  
            FROM 
            (SELECT  invoicereferencenumber AS ref, invoiceamount AS total, companyname AS company, customer.customerid AS id FROM customer, rental_invoice AS r WHERE customer.customerid=r.customerid 
            UNION 
            SELECT  invoicereferencenumber AS ref, invoiceamount AS total, companyname AS company, customer.customerid AS id FROM customer, sale_invoice AS s WHERE customer.customerid=s.customerid 
            UNION 
            SELECT  invoicereferencenumber AS ref, invoiceamount AS total, companyname AS company, customer.customerid AS id FROM customer, service_invoice AS se WHERE customer.customerid=se.customerid) 
            AS tmptotal 
            GROUP BY id
        ) TotalQuery LEFT JOIN
        (
            SELECT SUM(paid) AS paid, company, cid  
            FROM 
            (SELECT cash_amount AS paid, companyname AS company, customerid AS cid FROM payment_cash AS p, customer AS c WHERE cash_cus_id=customerid 
            UNION 
            SELECT cheque_value AS paid, companyname AS company, customerid AS cid FROM payment_cheque AS p, customer AS c WHERE cheque_cus_id=customerid 
            UNION 
            SELECT rtgs_value AS pain, companyname AS company, customerid AS cid FROM payment_rtgs AS p, customer AS c WHERE rtgs_cus_id=customerid) 
            AS tmppaid 
            GROUP BY cid 
        ) PaidQuery     ON  TotalQuery.id = PadiQuery.id
                        AND TotalQuery.company = PaidQuery.company

Assuming that you must have a Total to inver that you have paid something, this will return a list of all copany,id groupings with their total and the amount paid on this total.

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79889

You can combine the two queries by joining all the tables like this:

Select Sum(ri.invoicereferencenumber) + 
       Sum(si.invoicereferencenumber) +
       SUM(se.invoicereferencenumber) as Total,
       SUM(pc.cash_amount) + SUM(pch.cheque_value) + SUM(pr.rtgs_value) as Paid,
       c.Company, c.Id
from customer c
inner join rental_invoice  ri  on c.customerid = ri.customerid
inner join sale_invoice    si  on c.customerid = si.customerid
inner join service_invoice se  on c.customerid = se.customerid
inner join payment_cash    pc  on c.customerid = pc.cash_cus_id
inner join payment_cheque  pch on c.customerid = pch.cheque_cus_id
inner join payment_rtgs    pr  on c.customerid = pr.rtgs_cus_id
group by c.Company, c.Id

Upvotes: 0

Related Questions