CODEWITHSUNDEEP
sqlsqlite
vaahtlnirn1
vaahtlnirn1

Reputation: 58

Sum of two columns alongside separate select statement

I am working on an SQL task and I cannot figure out how to get the sum of two columns from the same table while displaying information from another table.

I have tried multiple things and have spent probably about two hours trying to figure this out.

I have two tables: Employees and Fuel. I displayed all of the employee's information.First SQL statement I had to make: 

SELECT firstname, lastname, title, registrationyear, make, model FROM Employees ORDER BY make;

My Employees table has the following columns: firstname, lastname, employeeid, make, model, registrationyear, title

My Fuel table has the following columns: currentprice, fueltype, fuelcost, mileage, mileagecount, fuelamount, employeeid, date

My instructions state: "A list that shows what cars the employees currently use (first SQL statement I made, so this one is DONE!) Like the above report but also the total amount of kilometers that the employees have driven and the total fuel cost." (this is the task that I am trying to make a statement for)

I have tried using LIKE, UNION, UNION ALL, etc. and the best that I have been able to do is listing the employee information and the totals ON TOP of the information instead of in two separate columns of their own alongside the other data in the query.

I am really stuck here. Could anyone please help me?

Upvotes: 0

Views: 341

Answers (2)

Stefano Zanini
Stefano Zanini

Reputation: 5916

This second task is muck more complex than the first one.

First of all, combining in a single row the columns from two or more tables is what join is for, so you will have to join the two tables based on employeeid. This will return you a table like this

employeeid | other emp fields | fuel date  | other fueld fields
1          | ...              | 01/01/2017 | ...
1          | ...              | 01/02/2017 | ...
2          | ...              | 01/01/2017 | ...
2          | ...              | 02/01/2017 | ...
2          | ...              | 04/03/2017 | ...

From here, you want the data from each employee combined with the sum of the rows from fuel related to that employee, and that's what group by is for.

When using group by you define a set of columns that defines the grouping criteria; everything else in your select statement will have to be grouped somehow (in your case with a sum), so that the columns in the group by stay unique.

Your final query would look like this

select  t1.firstname, t1.lastname, t1.title, t1.registrationyear, t1.make, t1.model,
        sum(t2.mileage) as total_milege,
        sum(t2.fuelcost * t2.fuelamount) as total_fuel_cost
from    Employees t1
join    Fuel t2
on      t1.employeeid = t2.employeeid
group by t1.firstname, t1.lastname, t1.title, t1.registrationyear, t1.make, t1.model

Note: I don't know the difference between mileage and mileagecount, so the part of my query involving those fields may need some tweaking.

Upvotes: 1

Brijesh
Brijesh

Reputation: 529

You can use Inner join & Group By clause as mentioned below. Let me know if you mean something else.

SELECT A.firstname, A.lastname, A.title, A.registrationyear, A.make, A.model,
SUM(B.Column_Having_Kilometer_Driven_Value)
FROM
Employee A
INNER JOIN Fuel B ON A.EmployeeID = B.EmployeeID
Group By A.EmployeeID, A.firstname, A.lastname, A.title, A.registrationyear, A.make, A.model

Upvotes: 0

Related Questions