Query result of an Alias column?

[hunterhawk231]

Hi could somebody please have a look at my SQl query I have written. I’m trying to design a fleet management database for my university course and im stuck this query. I apologise in advance for my lack of SQL knowledge as this is my first attempt.
Currently my query searches the “view” that I have created and calculates employees total mileage based on year, by subtracting their journeys end miles from there 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 25000 miles a year form the result that I have created. I not sure if this is even possible?
I have search all over the internet to but with no luck, any help would be so much appreciated or please just point me in the right direction.
Thank you

select * 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
) WHERE 'Total Miles Driven' >= 25000<img src="/community/uploads/default/original/3X/2/5/25dc3dd496375aef8c69cdc6c7b900e2a882b7ff.JPG" width="690" height="268">

[r937]

“Incorrect syntax near the keyword ‘WHERE’” tells you exactly where it barfed on your query, although it doesn’t say why

the problem is this – every derived table needs a name

a “derived table” is a subquery in the FROM clause

let’s call it Todd, okay?

[code]SELECT *
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 Todd
WHERE ‘Total Miles Driven’ >= 25000

[/code]

[r937]

in addition, while you cannot refer to a column alias in the WHERE clause, you ~can~ do so in the HAVING clause –

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 'Total Miles Driven' >= 25000