Anthony Anthony - 1 month ago 5
Scala Question

How to calculate cost by querying for a particular field in a row

If I have the following data

Cust No. | Action | Val
----------| --------------| ----
10 | Checked out | 1.0
10 | PAID | 40.0
10 | Checked In | 1.0
15 | Flew Away | 2.0
15 | PAID | 100.00
15 | Came back | 1.0
20 | PAID | 150.00
30 | Checked In | 1.0
30 | PAID | 50.00
30 | PAID | 10.00


How can I get the
SUM
of only the
PAID
values for each customer with a
Checked In
entry

i.e.

Cust No. | Total Paid
----------| --------------
30 | 60.00
10 | 40.00

Answer

This should work:

 select customer.customer_number, sum(value) as total_paid  from customers
    left join
    (
      select customer_number from users where action = 'Checked In' group by customer_number
    )checkins on checkins.customer_number = customers.customer_number 
    where checkins.customer_number is not null and customers.action = 'PAID'
    group by customers.customer_number
Comments