I am trying to write a query for select a specific year data from data table, but I am getting an error.
I am trying to reach the average of the hourly fee for cars parked in 2022. I wrote this query, but I am getting an error.
My query: (Not working)
SELECT AVG(HourlyFee) FROM ParkingFees
WHERE Region IN(SELECT Region FROM ParkingHistory
WHERE YEAR (Date) ='2022');
Error:
ERROR: function year(timestamp without time zone) does not exist
LINE 3: WHERE YEAR (Date) ='2022');
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 100
Here is the database:
CREATE TABLE ParkingHistory(RegNo varchar(7), Date timestamp(0), Hours int, Region varchar(9));
INSERT INTO ParkingHistory
(RegNo, Date, Hours, Region)
VALUES
('B17SNR', '2023-01-30 00:00:00', 15, 'Bucharest'),
('B02JSH', '2023-01-28 00:00:00', 8, 'Cluj'),
('B74BFK', '2023-01-30 00:00:00', 24, 'Cluj'),
('BH84JSB', '2022-12-26 00:00:00', 9, 'Bucharest'),
('IF92AAN', '2022-12-26 00:00:00', 7, 'Bucharest'),
('OT74ISH', '2022-12-14 00:00:00', 15, 'Cluj'),
('CT56UUH', '2022-12-11 00:00:00', 23, 'Ploiesti'),
('BR82IIS', '2022-12-05 00:00:00', 24, 'Bucharest'),
('B73YHT', '2022-12-05 00:00:00', 24, 'Bucharest'),
('B83UEH', '2022-12-05 00:00:00', 21, 'Bucharest');
CREATE TABLE ParkingFees(Region varchar(9), HourlyFee int);
INSERT INTO ParkingFees
(Region, HourlyFee)
VALUES
('Bucharest', 20),
('Cluj', 16),
('Ploiesti', 12),
('Pitesti', 16),
('Timisoara', 9);
CREATE TABLE Cars(RegNo varchar(7), Model varchar(7), IsResident int);
INSERT INTO Cars
(RegNo, Model, IsResident)
VALUES
('B17SNR', 'T-ROC', 1),
('B02JSH', 'Ibiza', 0),
('B74BFK', 'Ibiza', 1),
('BH84JSB', 'Sandero', 0),
('IF92AAN', 'Logan', 0),
('OT74ISH', 'T-ROC', 1),
('CT56UUH', 'Arona', 1),
('BR82IIS', 'Tiguan', 1),
('B73YHT', 'Logan', 1),
('B83UEH', 'Duster', 0);
CREATE TABLE CarModels(Model varchar(7), Manufacturer varchar(10), FabricationYear int);
INSERT INTO CarModels
(Model, Manufacturer, FabricationYear)
VALUES
('Logan', 'Dacia', 2016),
('Sandero', 'Dacia', 2018),
('Duster', 'Dacia', 2019),
('Leon', 'Seat', 2017),
('Ibiza', 'Seat', 2019),
('Ateca', 'Seat', 2020),
('Arona', 'Seat', 2021),
('Golf', 'Volkswagen', 2017),
('Polo', 'Volkswagen', 2019),
('Tiguan', 'Volkswagen', 2020),
('T-ROC', 'Volkswagen', 2021);
I am trying to find the average of the hourly fee for cars parked in 2022. How can I write the true query?
2
Answers
To get the year from a timestamp you need to use the EXTRACT function e.g.
You could use EXTRACT() as @NickW already mentioned, or use >= and < in your where condition: