CODEWITHSUNDEEP
sqlsqlite
Roman
Roman

Reputation: 25

Performing an aggregate operation (SUM) with the results of another query

I have two tables: 

Bookings (uid INTEGER, in_date DATE, paxname TEXT, acnumber INTEGER)

and 

Accounts (uid INTEGER, uidbooking INTEGER, acnumber INTEGER, amount_charged DOUBLE)

I would like to sum all the charges from the accounts table that belong to a subset of bookings.

The first select chooses the bookings in a certain perdiod of time...

Select bookings.uid, bookings.acnumber
WHERE bookings.status = 'checkedin' 
AND bookings.Indate > '2017-07-01'

Now the SECOND select should look for the charges of those bookings and SUM all the amounts of the accounts belonging to those previously selected bookings... 

Select SUM(amount_charged) FROM Accounts WHERE acnumber = bookings.acnumber

So, how can I get all the results with the bookings uid, account number AND in the same row, the sum of the account charges... ?

Thnks

Upvotes: 1

Views: 129

Answers (2)

Myonara
Myonara

Reputation: 1207

Using a join plus group syntax or the query as follows:

SELECT b.uid, b.acnumber,SUM(a.amount_charged) 
FROM bookings b, accounts a 
WHERE b.acnumber = a.acnumber 
AND b.status = 'checkedin' 
AND b.Indate > '2017-07-01'
GROUP BY b.uid,b.acnumber;

In the table bookings there is no acnumber, so maybe you have to exchange b.acnumber with b.account.

Upvotes: 0

dmg
dmg

Reputation: 4481

The theory

In SQL (not only in SQLite) these types of queries can be done in two different ways.

  1. Join first, then aggregate (GROUP BY), or

  2. Create a subquery, then aggregate only matching tuples.

While both methods will yield the same result, they might do it with significantly different performances. It will depend on the size of the database and the query evaluation algorithms that the SQL engine uses

The solution you need

  1. Using a join: I prefer explicit joins (make the code more readable)
    SELECT uid, acnumber, SUM(amount_charged) 
    FROM bookings join accounts USING(uid, acnumber)
    WHERE status = 'checkedin' AND b.Indate > '2017-07-01'
    GROUP BY uid, acnumber;
  1. Using a subquery:
    WITH Matching AS (SELECT uid, acnumber FROM bookings 
                      WHERE status = 'checkedin' and Indate > '2017-07-01')
    SELECT uid, acnumber, SUM(amount_charged)
    FROM Accounts
    WHERE (uid, acnumber) IN Matching
    GROUP BY uid, acnumber;

In general, if the size of the subquery is large, this query will run slower than the join. But if the subquery returns only few tuples, it will typically be faster.

Upvotes: 2

Related Questions