I will preface this by saying that I am completely self-taught and have been working with SQL for a few years at my job. By some stroke of luck I've never once had to use a subquery, but I've finally arrived at a problem that I believe requires one.
My goal is to come up with a sum total (dollar amount) grouped by day for work that is completed in our shop. Right now I have three tables, and they are as follows (with many useless fields removed):
TICKETNUMBER (A)
| PK | ID |
|---|---|
| 1 | 121 |
| 2 | 130 |
| 3 | 145 |
SERVICE (B)
| TICKET_ID | SERVICE_ID | RATE | QTY | DISCOUNT | COMPLETED_DATE |
|---|---|---|---|---|---|
| 121 | 1C | 149.00 | 1 | 0 | 3/7/2024 11:15:30 AM |
| 121 | 1J | 40.00 | 1 | 0 | 3/7/2024 11:26:22 AM |
| 130 | 2E | 0.00 | 1 | 0 | 3/7/2024 2:07:18 PM |
| 130 | 3F | 60.00 | 1 | 0 | 3/7/2024 2:07:18 PM |
| 145 | 4G | 89.00 | 1 | 0 | 3/8/2024 10:44:05 AM |
| 145 | 6H | 15.00 | 1 | 0 | 3/8/2024 10:44:05 AM |
PARTS (C)
| SERVICE_ID | PART | PRICE | QTY | DISCOUNT |
|---|---|---|---|---|
| 1J | XX7 | 6.00 | 1 | 0 |
| 1J | XX8 | 5.00 | 3 | 0 |
| 2E | XX3 | 79.99 | 1 | 0 |
Essentially, the ticket represents the entity itself that is being worked on. Multiple services can be linked to a single ticket and multiple parts can be linked to those services. Tickets can't exist without at least one service attached, but services CAN exist without parts attached. Even though there are no discounts in the examples above, I included those fields because sometimes there ARE discounts.
First, I constructed the following table to help visualize everything.
Select CAST(B.COMPLETED_DATE as DATE),A.ID, B.SERVICE_ID, B.RATE, B.QTY, B.DISCOUNT, C.PART, C.PRICE ,C.QTY, C.DISCOUNT
from A
join B on B.TICKET_ID=A.ID
full outer join C on C.SERVICE_ID=B.SERVICE_ID
order by A.ID DESC
and the result is:
| B.COMPLETED_DATE | A.ID | B.SERVICE_ID | B.RATE | B.QTY | B.DISCOUNT | C.PART | C.PRICE | C.QTY | C.DISCOUNT |
|---|---|---|---|---|---|---|---|---|---|
| 3/7/2024 | 121 | 1C | 149.00 | 1 | 0 | ||||
| 3/7/2024 | 121 | 1J | 40.00 | 1 | 0 | XX7 | 6.00 | 1 | 0 |
| 3/7/2024 | 121 | 1J | 40.00 | 1 | 0 | XX8 | 5.00 | 3 | 0 |
| 3/7/2024 | 130 | 2E | 0.00 | 1 | 0 | XX3 | 79.99 | 1 | 0 |
| 3/7/2024 | 130 | 3F | 60.00 | 1 | 0 | ||||
| 3/8/2024 | 145 | 4G | 89.00 | 1 | 0 | ||||
| 3/8/2024 | 145 | 6H | 15.00 | 1 | 0 |
Everything seems good so far, but the problem arises when I try to sum everything by date. If I try something like the following and group by date, it ends up summing both occurrences of the 40.00 charge for SERVICE_ID 1J because there are multiple parts attached to that single SERVICE_ID.
CAST(SUM((B.RATEB.QTY)-B.DISCOUNT)+SUM((C.PRICEC.QTY)-C.DISCOUNT) as DECIMAL(7,2))
and grouping by the date produces a total of 389.99 instead of the correct total of 349.99 for 3/7/2024.
My hunch is that i need some sort of subquery to sum the PARTS before it gets joined to the SERVICE table so that it doesn't duplicate rows in the SERVICE table, but I have no clue how to incorporate a subquery. Help!