CODEWITHSUNDEEP
mysql
oxyt
oxyt

Reputation: 1854

MYSQL: Count for every customer's visit

I am having problem to figure out how to create a query for this scenario:

Here is updated version of my question.

I have 3 tables.

`customers`: `id` , `name` , `address`

`cars`: `id` , `license_nr` , `make` , `customer_id`

`services`: `id` , `car_id` , `description`

Every time a customer comes for a service a new record made in services table. I want to know counts of services for each customer. There is no direct relation between in services and customers.

EDIT: Correction of a column name in services table.

Upvotes: 1

Views: 100

Answers (3)

Gremash
Gremash

Reputation: 8308

Here is the updated solution based on the updated question:

select count(s.id) as service_tickets, cust.id, cust.name, cust.address 
from customers cust 
left join cars c on c.customer_id = cust.id
left join services s on s.car_id = c.id
group by cust.id

I updated it so it would return 0 for customers who have no services on their cars.

Upvotes: 1

oxyt
oxyt

Reputation: 1854

I think I will answer my own question. Maybe someone else will need this. Thank you all for your efforts.

SELECT customers.name, COUNT(*) AS visit_count 
FROM services
JOIN cars ON cars.id = services.car_id
JOIN customers ON customer.id = cars.customer_id
GROUP BY customer_id

I get the result I want.

name         | visit count
Amal Hopkins |     1
Dean Leach   |     2

Upvotes: 1

Kenan Zahirovic
Kenan Zahirovic

Reputation: 1597

SELECT customer, COUNT(*) FROM table GROUP BY customer;

UPDATE:

If visit means a row in services table, then we are OK because we simply need to count rows in services table. Foreign keys are not a problem. In fact, we can use join to see customer's name. So, the syntax should be:

SELECT s.CustomerID, c.Name, COUNT(*) FROM services s
join customer c on c.id=s.CustomerID
GROUP BY CustomerID, c.Name

Upvotes: 0

Related Questions