CODEWITHSUNDEEP
phpmysqlcount
Shaun
Shaun

Reputation: 5531

Mysql query with count on another table

I have two tables, one that has transaction information such as id, status, price and date. I have another table that stores the number of items in that order as individual rows. So, say transaction 2 has 10 items, there will be 10 rows in the second table of different items. What im trying to do is run a query that lists transactions and the number of items sold in that transaction. I imagine this would require a count on the second table, but im not entirely sure of how to do it. This is the basic layout of the database

transaction id, date, price, discount, status
items: id, transaction_id, item_name, email, date_ordered, hash

Thanks in advance for all the help.

Upvotes: 0

Views: 181

Answers (2)

Rahul
Rahul

Reputation: 77926

You can do a left join on both tables like below

select t.*, tab.total_order
from transaction t
left join
(
select transaction_id, count(*) as total_order
from items
group by transaction_id
) tab on t.id = tab.transaction_id

Upvotes: 0

juergen d
juergen d

Reputation: 204914

Group by the columns in the transaction table you want to select. Then add a count of the items

select t.id, count(i.id) as item_count
from transaction t
left join items i on i.transaction_id = t.id
group by t.id

Upvotes: 1

Related Questions