P. S. da Silva Junior P. S. da Silva Junior - 6 days ago 8
SQL Question

SQL QUERY For totals from two different tables

I need to create a QUERY to return the totals from each car. Each key here is a purchase, and these purchases can have the Car ID in either the "Header" table or the "Lines" Table, because a purchase can be either for just one car, or for many cars. Is it possible to get something like the Result table as shown below?

Header:

╔═════╦════════╦═══════╗
║ Key ║ Total ║ Car ║
╠═════╬════════╬═══════╣
║ 1 ║ 100.00 ║ Car 1 ║
║ 2 ║ 350.00 ║ ║
║ 3 ║ 230.24 ║ Car 1 ║
║ 4 ║ 121.01 ║ Car 2 ║
║ 5 ║ 110.00 ║ ║
╚═════╩════════╩═══════╝


Lines:

╔═════╦══════╦═══════════╦═══════╗
║ Key ║ Line ║ LineTotal ║ Car ║
╠═════╬══════╬═══════════╬═══════╣
║ 1 ║ 0 ║ 100.00 ║ ║
║ 2 ║ 0 ║ 350.00 ║ Car 2 ║
║ 3 ║ 0 ║ 30.24 ║ Car 1 ║
║ 3 ║ 1 ║ 200.00 ║ Car 1 ║
║ 4 ║ 0 ║ 121.01 ║ ║
║ 5 ║ 0 ║ 10.00 ║ Car 1 ║
║ 5 ║ 1 ║ 100.00 ║ Car 2 ║
╚═════╩══════╩═══════════╩═══════╝


Result:

╔═══════╦════════╦═════╗
║ Car ║ Value ║ Key ║
╠═══════╬════════╬═════╣
║ Car 1 ║ 100.00 ║ 1 ║
║ Car 1 ║ 230.24 ║ 3 ║
║ Car 1 ║ 10.00 ║ 5 ║
║ Car 2 ║ 350.00 ║ 2 ║
║ Car 2 ║ 121.01 ║ 4 ║
║ Car 2 ║ 100.00 ║ 5 ║
╚═══════╩════════╩═════╝

Answer

Here you go:

SELECT A.Car, SUM(A.Total), A.Key
FROM HEADER A
GROUP BY A.Car,A.Key

UNION

SELECT B.Car, SUM(B.LineTotal), B.Key
FROM LINES B
GROUP BY B.Car,B.Key