Getting sum of one persons spending given one bar's pricing

Question

So I have the following tables

bills
billId | bar | drinker | date    | time
001    | B1  | P1      | 11/10/18| 21:58
002    | B1  | P1      | 11/11/18| 20:58
003    | B2  | P2      | 11/12/18| 21:57
004    | B1  | P1      | 11/12/18| 21:56

transactions                      Sells
billID| item | quantity           bar  |  item  | price
001   | bud  | 3                  b1   | bud    | 2.00
002   | bud  | 3                  b1   | hite   | 5.00
003   | coors| 1                  b2   | coors  | 5.50
004   | hite | 3

Successfully managed to connect these two tables because they both share a billId using this query and get this result:

SELECT b.billId, b.bar, b.drinker, t.item, t.quantity 
from bills b, transactions t 
WHERE b.billId = t.billID 
ORDER BY b.drinker;

billId | bar | drinker | item | quantity
001    | B1  | P1      | bud  | 3
002    | B1  | P1      | bud  | 3
003    | B2  | P2      | bud  | 1
004    | B1  | P1      | hite | 3

So now I want to take my query and calculate the total price for person P1 using the respective prices and quantity. I am just confused on how I would get the total sales for P1. I the one thing that is throwing me off is including the quantity in a persons total for a particular bar.

What data type is your `date` column ? Also, please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) — Madhur Bhaiya, Nov 16 '18 at 17:45

Barbaros Özhan · Accepted Answer · 2018-11-16T17:56:05.403

2

You may JOIN three tables to get your results :

SELECT b.drinker, b.bar, sum(t.quantity) as quantity,  
       sum(t.quantity * s.price) as price
  FROM bills b
  LEFT JOIN transactions t on ( b.billId = t.billID )
  LEFT JOIN sells s on ( t.item = s.item )
 GROUP BY b.drinker, b.bar
 ORDER BY b.drinker;

edited Nov 16 '18 at 17:56

answered Nov 16 '18 at 17:49

Barbaros Özhan

59,113
10
31
55

score 0 · Answer 2 · answered Nov 16 '18 at 17:49

You could use the aggregation function sum() and group by bar and drinker

    select bar, drinker, sum(quantity*price) total
    from  (
        SELECT b.billId, b.bar, b.drinker, t.item, t.quantity, t.price 
        from bills b
        INNER JOIN  transactions t  ON b.billId = t.billID 
    ) m 
    group by bar, drinker
    ORDER BY drinker;

Getting sum of one persons spending given one bar's pricing

2 Answers2