CODEWITHSUNDEEP
mysqlsqljoin
Pablo Picasso
Pablo Picasso

Reputation: 23

How to select data from one table and select count to another table and combine it in MySQL?

In one fruit there can be multiple tickets that can be raised. I need to display the number of tickets raised per fruit. Their key field is the fruit_id.

sample output picture

Upvotes: 2

Views: 1448

Answers (2)

sanjaya
sanjaya

Reputation: 222

SELECT Fruit.Fruit ID,Fruit.Fruit Name, count(Ticket.Ticket Id) as match_rows FROM Fruit LEFT Join Ticket on (Fruit.Fruit ID = Ticket.Fruit ID) group by Fruit.Fruit Id ORDER BY Fruit.Fruit ID DESC

Upvotes: 0

Edgar Ramírez Mondragón
Edgar Ramírez Mondragón

Reputation: 3001

If I have the following tables:

fruit

id    name
 1    apple
 2    orange

tickets

id  fruit_id
 1         1
 2         1
 3         2
 4         2
 5         2

Then I would use the following SQL syntax to output a table like the one you need:

SELECT fruit.id, fruit.name, COUNT(tickets.id)
FROM tickets
LEFT JOIN fruit ON fruit.id = tickets.fruit_id
GROUP BY fruit.id;

Output:

id     name   COUNT(tickets.id)
 1    apple                  2
 2   orange                  3

Upvotes: 1

Related Questions