Hello,
I'm currently running into trouble using count in a
substraction in a sql statement.
Given:
SELECT COUNT(*) FROM people WHERE flight='172B';
gives me the number of people who have currently booked flight
172B.
Than I have
SELECT max_seats FROM flights WHERE flight = '172B';
the maximum number of seats for that flight.
Doing two seperate Queries, it's obvious to get the number of
free seats.
How can I just construct ONE sql statement which just gives me
back the number of free seats for flight '172B' ?
I tried, without success the following:
SELECT max_seats - COUNT( SELECT * FROM PEOPLE WHERE flight='172B')
FROM flights WHERE flight = '172B';
Ok, somewhere I read that nested select statements are not
supported in mysql. But, how can I then get the free seats back
with just one statement ?
SELECT max_seats - Count(DISTINCT unique_customer_identifier)
FROM flights, people
WHERE flight = '172B';
The FROM clause creates a Cartesian product, so you need the DISTINCT
in Count() to eliminate duplicate customer records.
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
3 messages in com.mysql.lists.mysqlRe: Using COUNT to substract result f...