I need help using count for a query
i need to select A list showing the customer id, title, first name and surname of each customer who has hired a car, sorted alphabetically by customer surname together with a count of the number of bookings each of them has placed.
I've done the first part but not sure where to put count for the number of bookings placed.
Here are the tables are
create table customer (customer_id char(4) primary key not null, customer_sname varchar (30) not null, customer_fname varchar (30) not null, customer_title varchar (6) not null, customer_address1 varchar (35) not null, customer_address2 varchar (35) null, customer_postcode varchar (25) null, customer_phone varchar (30) null, customer_email varchar (40) null, customer_di varchar (40) not null) ENGINE=InnoDB; create table car_booking (booking_id INTEGER AUTO_INCREMENT primary key not null, car_id char (4) not null, customer_id char (4) not null, hire_sdate date not null, hire_edate date not null) engine=innodb
I have done this
select customer_id, customer_title, Customer_fname, customer_sname from customer where customer_id in (select customer_id from car_booking ) order by customer_sname asc
This will require the use of an aggregate function (COUNT), a GROUP BY clause, and a LEFT JOIN to the CAR_BOOKING table:
SELECT c.customer_id, c.customer_title, c.customer_fname, c.customer_sname, COALESCE(COUNT(*), 0) AS num_bookings FROM CUSTOMER c LEFT JOIN CAR_BOOKING cb ON cb.customer_id = c.customer_id GROUP BY c.customer_id, c.customer_title, c.customer_fname, c.customer_sname ORDER BY c.customer_sname
Because there are columns not wrapped in an aggregate function like COUNT, those columns need to be defined in the GROUP BY clause.
I used a LEFT OUTER JOIN to the CAR_BOOKINGS table to return customers who do not have any bookings - these records will show zero as the value in the num_booking column. You can omit the LEFT keyword in the query to return only customers & counts with bookings. COALESCE is a standard function to convert a null value into a desired one - in this case, the count being null...
In SQL Server, I'd use:
select c.customer_id, c.customer_title, c.customer_fname, c.customer_sname, count (*) from cutomer c, car_booking cb where cb.customer_id = c.customer_id group by c.customer_id, c.customer_title, c.customer_fname, c.customer_sname
Not intimately familar with MySQL so it may play out a little differently, but that's the general idea.
select customer.customer_id, customer.customer_title, customer.customer_fname, customer.customer_sname, count(*) as Bookings from customer JOIN car_booking ON customer.customer_id = car_booking.customer_id GROUP BY customer.customer_id, customer.customer_title, customer.Customer_fname, customer.customer_sname order by customer_sname asc