Mysql query with count on another table

Question

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.

juergen d · Accepted Answer · 2014-10-24 17:50:43Z

1

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

answered Oct 24, 2014 at 17:50

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shaun Over a year ago

Thanks, that seems to work. Ill mark as answer as soon as I can

Rahul · Accepted Answer · 2014-10-27 08:10:39Z

0

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

edited Oct 27, 2014 at 8:10

answered Oct 24, 2014 at 17:53

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

2 Comments

Shaun Over a year ago

Hmm, I realized that the answer above only returns one result (as it doesnt have a group by). For the page I was working on it was fine, but on the page im working on now, I need all the rows. Im trying to use your code, but for some reason its telling me the fields dont exist for tab.transaction_id.

Rahul Over a year ago

@Shaun, ahh! that's a silly one. You got that error cause transaction_id was not included in the select list of the inner query. Try the edited query and it should work fine now.

Collectives™ on Stack Overflow

Mysql query with count on another table

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related