CODEWITHSUNDEEP
sqldatabaseoracle-database
Astros
Astros

Reputation: 179

Append a column from a query result to another in SQL

My first query is:

SELECT client.name, client.balance, SUM(payment.amount) AS paid
FROM client, payment
WHERE payment.clientid = client.client_id
GROUP BY client.name, client.balance

That gives the following results:

|Name  | Balance | Paid|
|------|---------|-----|
|name1 |5000     |125  |
|name2 |6000     |150  |

My second Query is:

SELECT client.name, client.balance,  SUM(price) AS due
FROM client, reservation, trip, excursion 
WHERE client.client_id = reservation.client_id AND
        trip.tripid = reservation.tripid AND
        trip.exname = excursion.exname
GROUP BY client.name, client.balance

And it gives:

|Name  | Balance | Due |
|------|---------|-----|
|name1 |5000     |250  |
|name2 |6000     |100  |
|name3 |3000     |100  |
|name4 |1000     |100  |

How can I merge these two results to get a single table that contains both the paid and due value:

|Name  | Balance | Due | Paid|
|------|---------|-----|-----|
|name1 |5000     |250  |125  |
|name2 |6000     |100  |150  |
|name3 |3000     |100  |     |
|name4 |1000     |100  |     |

Upvotes: 0

Views: 168

Answers (2)

The Impaler
The Impaler

Reputation: 48780

The simplest way to get the result you want is to treat those two queries as "table expressions" and join them.

For example, you can do:

select
  x.*,
  y.paid
from (
  SELECT client.name, client.balance,  SUM(price) AS due
  FROM client
  JOIN reservation ON client.client_id = reservation.client_id
  JOIN trip ON trip.tripid = reservation.tripid
  JOIN excursion ON trip.exname = excursion.exname
  GROUP BY client.name, client.balance
) x
left join (
  SELECT client.name, client.balance, SUM(payment.amount) AS paid
  FROM client
  JOIN payment ON payment.clientid = client.client_id
  GROUP BY client.name, client.balance
) y on y.name = x.name

I rephrased the JOINs according to modern standards. Avoid that comma-separated FROM syntax from the 80s.

Now, I'm sure there's a shorter way that can be simpler.

Upvotes: 2

mayurssoni
mayurssoni

Reputation: 66

Below code snippet should work

SELECT
    client.name,
    client.balance,
    SUM(price) AS due,
    SUM(payment.amount) AS paid
FROM
    client,
    payment,
    reservation,
    trip,
    excursion
WHERE
    payment.clientid = client.client_id
    AND client.client_id = reservation.client_id
    AND trip.tripid = reservation.tripid
    AND trip.exname = excursion.exname
GROUP BY
    client.name,
    client.balance

Upvotes: 0

Related Questions