CODEWITHSUNDEEP
sqloracle-database
Kelsie
Kelsie

Reputation: 13

Combining two queries Oracle SQL

I'm trying to combine my two queries that work perfectly fine individually. But I'm stuck on trying to get them together to work as one and bring out the desired results. The two queries are:

select clientid, sum(fee) as "Total Spent"
from   bookings
group by clientid;

select l.clientid, sum(m.price * l.quantity) as "Total Spent"
from   lineitems l
       join merchandise m on m.merchid = l.merchid
group by l.clientid;

So the end goal is to combine the amount of money each client has spent for both bookings and purchasing. i.e. Client ID 12 has spent $450 on bookings and $85 on products; so that would total to $535.

The set of data is this:

Bookings Table:

+----------+-------+------------+----------+-----------+---------+------------+
| ClientId | Tour  | EventMonth | EventDay | EventYear | Payment | DateBooked |
+----------+-------+------------+----------+-----------+---------+------------+
|       12 | South | Feb        |       20 |      2016 |     225 | 19/02/2016 |
|       12 | West  | Mar        |        5 |      2016 |     225 | 3/03/2016  |
+----------+-------+------------+----------+-----------+---------+------------+

LineItems Table:

+----------+-------+------------+----------+-----------+---------+-----+
| ClientID | Tour  | EventMonth | EventDay | EventYear | MerchId | Qty |
+----------+-------+------------+----------+-----------+---------+-----+
|       12 | South | Feb        |       20 |      2016 |      20 |   1 |
+----------+-------+------------+----------+-----------+---------+-----+

Merchandise Table:

+---------+----------+------------+-------+
| MerchID | Category | ProdName   | Price | 
+---------+----------+------------+-------+
|      20 | A        | Highway    |    85 |    
+---------+----------+------------+-------+

Any help would be muchly appreciated

Upvotes: 1

Views: 70

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use join:

SELECT b.*, m.*, b.totalspent + c.totalspent
FROM (SELECT CLIENTID, SUM(FEE) AS TotalSpent
      FROM BOOKINGS2017
      GROUP BY CLIENTID
     ) b JOIN
     (SELECT L.CLIENTID, SUM(M.PRICE * L.QUANTITY) AS TotalSpent
      FROM LINEITEM2017 L JOIN
           MERCHANDISE2017 M
           ON L.MERCHID = M.MERCHID
      GROUP BY L.CLIENTID
    ) m
    USING (CLIENTID);

You may need an outer join if the tables have different sets of clients.

Upvotes: 1

William Robertson
William Robertson

Reputation: 15991

This is essentially the same as Gordon's answer but with your sample data inline and a grand total:

-- Your sample data:
with bookings (clientid, tour, eventmonth, eventday, eventyear, payment, datebooked ) as
     ( select 12, 'South', 'Feb', 20, 2016, 225, date '2016-02-19' from dual union all
       select 12, 'West',  'Mar', 5,  2016, 225, date '2016-03-03' from dual union all
       select  2, 'West',  'Mar', 5,  2016, 225, date '2016-03-03' from dual union all
       select  2, 'West',  'Mar', 6,  2017, 225, date '2016-03-03' from dual union all
       select  2, 'West',  'Mar', 7,  2018, 225, date '2016-03-03' from dual )
   , lineitems (clientid, tour, eventmonth, eventday, eventyear, merchid, quantity) as
     ( select 12, 'South', 'Feb', 20, 2016, 20, 1 from dual )
   , merchandise (merchid, category, prodname, price) as
     ( select 20, 'A', 'Highway', 85 from dual )
--
-- Actual query starts here
--
select b.clientid
     , bookings_total
     , coalesce(merchandise_total,0) as merchandise_total
     , bookings_total + coalesce(merchandise_total,0) as grand_total
from   ( select clientid, sum(payment) as bookings_total
         from   bookings
         group by clientid ) b
       left join
        ( select l.clientid, sum(l.quantity * m.price) as merchandise_total
          from   lineitems l
                 join merchandise m on m.merchid = l.merchid
          group by clientid ) lm
        on  lm.clientid = b.clientid;


CLIENTID BOOKINGS_TOTAL MERCHANDISE_TOTAL GRAND_TOTAL
-------- -------------- ----------------- -----------
      12            450                85         535
       2            675                 0         675

Upvotes: 0

Related Questions