Help with query

[brixton]

Hello!

I have a DB setup which simplified looks like this:

PRODUCT(id, owner_id)
OWNER(id)

What I want to do is to produce a list of how many products each owner has, and then sort it in some way, all in a single query.

How do I go about this?

Thanks!

[sknake]

I'm guessing that means you have two tables? Please post an actual schema if not.

Select Owner.Id, Sum(Product.Qty) As Qty
From Owner inner join product on (owner.id=product.owner_id)
Group By Owner.Id
Order By Owner.Id

[brixton]

Thanks, yes, there are two tables.

Question: where does the "Product.Qty" come from? Is it created in the inner join? I don't have the database in place at the moment so I can't test it piece-by-piece.

[mail2saion]

TRY WITH THIS ONE:

SELECT OWNER.ID,COUNT(*) TOTALPRODUCT FROM PRODUCT,OWNER WHERE PRODUCT.OWNER_ID=OWNER.ID

[brixton]

That won't do it. It will sum together all the products that have an owner. I'm liking the previous suggestion more but I'd like to find out where the Product.Qty comes from.

[mail2saion]

Try to understand the above sql. because product table doesnot contain 2 products in the single row. so that this will definitely work.

[mail2saion]

EXAMINE THE BELOW QUERY BASED ON YOUR SUPPLIED TABL DETAILS:

SELECT owner_id,count(*) from OWNER GROUP BY OWNER_ID

NOW EXAMINE WHAT THE QUERY RETURN.

[brixton]

Problem is, I don't have any database to test it on. So it's a theoretical problem.

[sknake]

All of the queries you have been given are acceptable solutions, but they may be hard to read. I will post my original query in 2 ways depending on your situation.

If your product table has a Qty field, such as this is a shipping product table or such, you will use the original query

Select Owner.Id, Sum(Product.Qty) As Qty
From Owner inner join product on (owner.id=product.owner_id)
Group By Owner.Id
Order By Owner.Id

If your product table does not have a Qty field, and represents one product per row, use this (same query as mail2saion but written differently). If this query makes sense then you should use the query mail2saion provided, it makes more sense... i just included this since you seemed to understand this syntax more.

Select Owner.Id, Count(*) As Qty
From Owner inner join product on (owner.id=product.owner_id)
Group By Owner.Id
Order By Owner.Id

[brixton]

Managed to set up a MySQL database and test my way through. Went with the 2nd option in the post above. Thanks both of you for your help!