CODEWITHSUNDEEP
sql
tbone231
tbone231

Reputation: 1

SQL Query for Vehicle Mileage

I’m trying to design a fleet management database for my university course.

Currently my query searches the “view” that I have created and calculates employees total mileage based on year, by subtracting their journey's end miles from their start miles, which works and groups them in a result. But what I can’t do, no matter how hard I try, is to then query what employees do more than 25,000 miles a year from the result that I have created. I not sure if this is even possible to query the result of an alias column?

SELECT Assigned_Vehicle_id "Assigned Vehicle id", 
       Payroll_Number "Payroll Number", First_Name "First Name",
       Surname, Vehicle_Registration "Vehicle Registration",
       Manufacturer_Name "Manufacturer Name", Model_Name "Model Name",
       SUM(End_Miles - Start_Miles) "Total Miles Driven"
  FROM Destinations
 WHERE YEAR(Start_Date) = 2016
 GROUP BY Assigned_Vehicle_id, Payroll_Number, First_Name, Surname,
       Vehicle_Registration, Manufacturer_Name, Model_Name;

Upvotes: 0

Views: 1312

Answers (2)

Jason
Jason

Reputation: 945

Try this. Notice the HAVING clause after the GROUP BY

 SELECT Assigned_Vehicle_id "Assigned Vehicle id", 
 Payroll_Number "Payroll Number", First_Name "First Name", Surname,
 Vehicle_Registration "Vehicle Registration", Manufacturer_Name 
 "Manufacturer Name",
 Model_Name "Model Name", SUM(End_Miles - Start_Miles) "Total Miles Driven" 
 FROM Destinations 
 WHERE YEAR(Start_Date) = 2016 
 GROUP BY 
 Assigned_Vehicle_id, Payroll_Number, First_Name, Surname, 
 Vehicle_Registration, Manufacturer_Name, Model_Name
 HAVING SUM(End_Miles - Start_Miles) > 25000

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 754710

In some ways, a HAVING clause as suggested by Jason in his answer is good, simple, and quite probably what's expected — even though it involves writing out the expression twice rather than allowing you to use the alias. Some DBMS do, I believe, allow you to use an output column alias in subsequent clauses such as the GROUP BY or HAVING clauses, but the standard does not, so most DBMS do not either.

However, there is another alternative which would probably work:

SELECT "Assigned Vehicle id", 
       "Payroll Number", "First Name",
       Surname, "Vehicle Registration",
       "Manufacturer Name", "Model Name",
       "Total Miles Driven"
  FROM (SELECT Assigned_Vehicle_id "Assigned Vehicle id", 
               Payroll_Number "Payroll Number", First_Name "First Name",
               Surname, Vehicle_Registration "Vehicle Registration",
               Manufacturer_Name "Manufacturer Name", Model_Name "Model Name",
               SUM(End_Miles - Start_Miles) "Total Miles Driven"
          FROM Destinations
         WHERE YEAR(Start_Date) = 2016
         GROUP BY Assigned_Vehicle_id, Payroll_Number, First_Name, Surname,
               Vehicle_Registration, Manufacturer_Name, Model_Name
       ) AS Mileages_In_2016
 WHERE "Total Miles Driven" > 25000

This runs your original query as 'sub-query' in the FROM clause, which allows you to write a filter in the outer query against the "Total Miles Driven" column in the outer query.

Upvotes: 0

Related Questions