Convert SQL to PostgreSQL

Question

This shows the average occupancy (num_pax) of flights that have an airport of "SPAIN" as an exit

How can I convert this for PostgreSQL please?

SELECT AVG(number_pax) 
FROM flights f,airports a 
WHERE f.departure_airport=a.id_airport
AND a.country LIKE "ESPAÑA";

As an aside, I have immense problems with tilde in my (rather old) RDBMS on windows 7. — Strawberry, Dec 12 '19 at 14:31
You keep posting questions asking *others* to convert queries to PostgreSQL. That's not what SO is for. That's why your questions are getting downvoted. Do you have any actual problems? Have you tried anything? — Panagiotis Kanavos, Dec 12 '19 at 14:32

score 2 · Accepted Answer · answered Dec 12 '19 at 14:22

2

Your immediate issue is that Postgres uses double quotes for identifiers. You want to use the standard single quotes instead (that you should be using anyway).

I would also warmly recommend to use proper, explicit joins instead of old-school, implicit joins.

Finally, your LIKE condition has no joker character on the right side, so it is equivalent to an equality check.

SELECT AVG(number_pax) 
FROM flights f
INNER JOIN airports a ON f.departure_airport = a.id_airport
WHERE a.country = 'ESPAÑA';

answered Dec 12 '19 at 14:22

GMB

216,147
25
84
135

Most databases use double quotes for identifiers so I wonder where this query came from? Or is it an assighment? – Panagiotis Kanavos Dec 12 '19 at 14:31
@PanagiotisKanavos: I would guess this was a MySQL query. It allows double quotes for strings. – GMB Dec 12 '19 at 14:32
1

And now I see the OP keeps asking people to translate queries for him ........ – Panagiotis Kanavos Dec 12 '19 at 14:33
Does MySQL allow HAVING without GROUP BY? That's what the previous question was about – Panagiotis Kanavos Dec 12 '19 at 14:35
@PanagiotisKanavos: *Does MySQL allow HAVING without GROUP BY*: yes, [I think it does](https://stackoverflow.com/questions/6924896/having-without-group-by) – GMB Dec 12 '19 at 14:46

Convert SQL to PostgreSQL

1 Answers1