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!

Re: Help with query 80 80

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
Re: Help with query 80 80

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.

Re: Help with query 80 80

TRY WITH THIS ONE:

SELECT OWNER.ID,COUNT(*) TOTALPRODUCT FROM PRODUCT,OWNER WHERE PRODUCT.OWNER_ID=OWNER.ID
Re: Help with query 80 80

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.

Re: Help with query 80 80

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

Re: Help with query 80 80

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.

Re: Help with query 80 80

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

Re: Help with query 80 80

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
Re: Help with query 80 80

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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.