Tricky (MS)SQL query with aggregated functions

Question

I have these three tables:

table_things:      [id]
table_location:    [id]
                   [location]
                   [quantity]
table_reservation: [id]
                   [quantity]
                   [location]
                   [list_id]

Example data:

table_things:
id
1
2
3

table_location
id     location     quantity
1      100          10
1      101          4
2      100          1

table_reservation
id     quantity     location     list_id
1      2            100          500
1      1            100          0
2      1            100          0

They are connected by [id] being the same in all three tables and [location] being the same in table_loation and table_reservation.

[quantity] in table_location shows how many ([quantity]) things ([id]) are in a certain place ([location]).

[quantity] in table_reservation shows how many ([quantity]) things ([id]) are reserved in a certain place ([location]).

There can be 0 or many rows in table_reservation that correspond to table_location.id = table_reservation_id, so I probably need to use an outer join for that.

I want to create a query that answers the question: How many things ([id]) are in this specific place (WHERE table_location=123), how many of of those things are reserved (table_reservation.[quantity]) and how many of those that are reserved are on a table_reservation.list_id where table_reservation.list_id > 0.

I can't get the aggregate functions right to where the answer contains only the number of lines that are in table_location with the given WHERE clause and at the same time I get the correct number of table_reservation.quantity.

If I do this I get the correct number of lines in the answer:

SELECT table_things.[id],
    table_location.[quantity],
    SUM(table_reservation.[quantity]
FROM table_location
INNER JOIN table_things ON table_location.[id] = table_things.[id]
RIGHT OUTER JOIN table_reservation ON table_things.location = table_reservation.location
WHERE table_location.location = 100
GROUP BY table_things.[id], table_location[quantity]

But the problem with that query is that I (of course) get an incorrect value for SUM(table_reservation.[quantity]) since it sums up all the corresponding rows in table_reservation and posts the same value on each of the rows in the result.

The second part is trying to get the correct value for the number of table_reservation.[quantity] whose list_id > 0. I tried something like this for that, in the SELECT list:

(SELECT SUM(CASE WHEN table_reservation.list_id > 0 THEN table_reservation.[quantity] ELSE 0 END)) AS test

But that doesn't even parse... I'm just showing it to show my thinking.

Probably an easy SQL problem, but it's been too long since I was doing these kinds of complicated queries.

score 1 · Accepted Answer · edited Jul 29 '20 at 18:27

For your first two questions:

How many things ([id]) are in this specific place (WHERE table_location=123), how many of of those things are reserved (table_reservation.[quantity])

I think you simply need a LEFT OUTER JOIN instead of RIGHT, and an additional join predicate for table_reservation

SELECT  l.id,
        l.quantity,
        Reserved = SUM(ISNULL(r.quantity, 0))
FROM    table_location AS l
        INNER JOIN table_things AS t
            ON t.id = l.ID
        LEFT JOIN table_reservation r
            ON r.id = t.id
            AND r.location = l.location
WHERE   l.location = 100
GROUP BY l.id, l.quantity;

N.B I have added ISNULL so that when nothing is reserved you get a result of 0 rather than NULL. You also don't actually need to reference table_things at all, but I am guessing this is a simplified example and you may need other fields from there so have left it in. I have also used aliases to make the query (in my opinion) easier to read.

For your 3rd question:

and how many of those that are reserved are on a table_reservation.list_id where table_reservation.list_id > 0.

Then you can use a conditional aggregate (CASE expression inside your SUM):

SELECT  l.id,
        l.quantity,
        Reserved = SUM(r.quantity),
        ReservedWithListOver0 = SUM(CASE WHEN r.list_id > 0 THEN r.[quantity] ELSE 0 END)
FROM    table_location AS l
        INNER JOIN table_things AS t
            ON t.id = l.ID
        LEFT JOIN table_reservation r
            ON r.id = t.id
            AND r.location = l.location
WHERE   l.location = 100
GROUP BY l.id, l.quantity;

As a couple of side notes, unless you are doing it for the right reasons (so that different tables are queried depending on who is executing the query), then it is a good idea to always use the schema prefix, i.e. dbo.table_reservation rather than just table_reservation. It is also quite antiquated to prefix your object names with the object type (i.e. dbo.table_things rather than just dbo.things). It is somewhat subject, but this page gives a good example of why it might not be the best idea.

Thanks. I saw your reply after I had written my own reply (to my own question, doh!). I thought RIGHT joins took all rows from the first (left) table and matched them with the rows from the second (right) table if there are any matches? So if I do a LEFT join then I shouldn't get any rows if there are no reservations? I haven't tested that yet myself though (because my test data set always has rows in the second (right) table). But please let me know if I'm not thinking correctly. — N. Little, Oct 19 '16 at 12:24
This diagrams [in this answer](http://stackoverflow.com/a/20298671/1048425) explain the join types quite nicely. It is definitely a better representation than I could put into words anyway. — GarethD, Oct 19 '16 at 12:30
Thanks man. You are absolutely correct in your reply. I have marked it as answered. Sometimes you stare so much at your own code you can't see the simple things. — N. Little, Oct 19 '16 at 12:31

score 0 · Answer 2 · answered Oct 19 '16 at 12:18

You can use a query like the following:

SELECT tt.[id],
       tl.[quantity],
       tr.[total_quantity],
       tr.[partial_quantity]              
FROM table_location AS tl
INNER JOIN table_things AS tt ON tl.[id] = tt.[id]
LEFT JOIN (
   SELECT id, location,
          SUM(quantity) AS total_quantity,
          SUM(CASE WHEN list_id > 0 THEN quantity ELSE 0 END) AS partial_quantity
   FROM table_reservation
   GROUP BY id, location
) AS tr ON tl.id = tr.id AND tl.location = tr.location
WHERE tl.location = 100

The trick here is to do a LEFT JOIN to an already aggregated version of table table_reservation, so that you get one row per id, location. The derived table uses conditional aggregation to calculate field partial_quantity that contains the quantity where list_id > 0.

Output:

id  quantity  total_quantity  partial_quantity
-----------------------------------------------
1   10        3               2
2   1         1               0

Thanks! You have much the same (correct) points as GarethD did. Well done. — N. Little, Oct 19 '16 at 12:33

N. Little · Answer 3 · 2016-10-19T12:32:48.407

This was a classic case of sitting with a problem for a few hours and getting nowhere and then when you post to stackoverflow, you suddenly come up with the answer. Here's the query that gets me what I want:

SELECT table_things.[id],
    table_location.[quantity],
    SUM(table_reservation.[quantity],
    (SELECT SUM(CASE WHEN table_reservation.list_id > 0 THEN ISNULL(table_reservation.[quantity], 0) ELSE 0 END)) AS test
FROM table_location
INNER JOIN table_things ON table_location.[id] = table_things.[id]
RIGHT OUTER JOIN table_reservation ON table_things.location = table_reservation.location AND table_things.[id] = table_reservation.[id]
WHERE table_location.location = 100
GROUP BY table_things.[id], table_location[quantity]

Edit: After having read GarethD's reply below, I did the changes he suggested (to my real code, not to the query above) which makes the (real) query correct.

Tricky (MS)SQL query with aggregated functions

3 Answers3