I need help using count for a query

Question

Hi all,

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

Thanks

Answer 1

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.

Answer 2

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

Answer 3

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