9 Replies - 339 Views - Last Post: 01 April 2017 - 03:08 PM

#1 tbone8888  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 01-April 17

Run a search on a Query Result

Posted 01 April 2017 - 11:50 AM

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 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;


Is This A Good Question/Topic? 0
  • +

Replies To: Run a search on a Query Result

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: Run a search on a Query Result

Posted 01 April 2017 - 12:04 PM

Add a where clause for that additional question
Was This Post Helpful? 0
  • +
  • -

#3 tbone8888  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 01-April 17

Re: Run a search on a Query Result

Posted 01 April 2017 - 12:23 PM

View Postastonecipher, on 01 April 2017 - 12:04 PM, said:

Add a where clause for that additional question


Hi thank you for your reply, I have tryied this adding [WHERE > 25000] allready but i get the error message "Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WHERE'." I know what i want to do but I just dont understand the syntax enough.

Thank you
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
WHERE > 25000
GROUP BY Assigned_Vehicle_id, Payroll_Number, First_Name, Surname, Vehicle_Registration, Manufacturer_Name, Model_Name;


Was This Post Helpful? 0
  • +
  • -

#4 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: Run a search on a Query Result

Posted 01 April 2017 - 12:26 PM

Think about that again...

Where where?
Was This Post Helpful? 0
  • +
  • -

#5 tbone8888  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 01-April 17

Re: Run a search on a Query Result

Posted 01 April 2017 - 12:35 PM

Hi this is what i did last night but I get the error "Msg 207, Level 16, State 1, Line 6
Invalid column name 'Total Miles Driven'." I know this is because i dont have a colum with the name 'Total Miles Driven' but i dont know how to query the 'Total Miles Driven' colum the query creates.

am I close? or miles away?

Thank you


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 AND "Total Miles Driven" > 25000
GROUP BY Assigned_Vehicle_id, Payroll_Number, First_Name, Surname, Vehicle_Registration, Manufacturer_Name, Model_Name;


This post has been edited by andrewsw: 01 April 2017 - 01:28 PM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#6 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: Run a search on a Query Result

Posted 01 April 2017 - 01:56 PM

Using a nested query, so that you can use the aliased column. Something like this should work


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

Was This Post Helpful? 0
  • +
  • -

#7 tbone8888  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 01-April 17

Re: Run a search on a Query Result

Posted 01 April 2017 - 02:11 PM

Sadly not I must be missing some as im getting error "Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'WHERE'."

I really appreciate your help your help with this as I have been trying for days to get this working.

Just to confirm the "Total Miles Driven" colum is referred to as a aliased column?

Thanks

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

Was This Post Helpful? 0
  • +
  • -

#8 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: Run a search on a Query Result

Posted 01 April 2017 - 02:15 PM

Sorry, alias the nested query.
Was This Post Helpful? 0
  • +
  • -

#9 tbone8888  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 01-April 17

Re: Run a search on a Query Result

Posted 01 April 2017 - 02:22 PM

Thanks I'll have a look into that.
Was This Post Helpful? 0
  • +
  • -

#10 tbone8888  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 01-April 17

Re: Run a search on a Query Result

Posted 01 April 2017 - 03:08 PM

Thank you for your help but from what i have read, people are saying you can't reference subquery alias. By im looking at it from the wrong angle.

Thanks
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1