MySQL group by two columns

[scim1971]

Hi,
I have a table storing items bought from an online shop. There is one line for each item.
Each line contains (amongst other things) - the Id of the product bought, the Id of the order and the product name e.g.

IdProduct (INT)
IdOrder (INT)
ProdTitle (VARCHAR)

sample data:
IdProduct = 1
IdOrder = 10
ProdTitle = “Product A”

IdProduct = 1
IdOrder = 11
ProdTitle = “Product A”

IdProduct = 2
IdOrder = 11
ProdTitle = “Product B”

IdProduct = 1
IdOrder = 13
ProdTitle = “Product A”

IdProduct = 3
IdOrder = 13
ProdTitle = “Product C”

In my sample data, Product A (ID = 1) is in three orders and Product B (ID = 2) and Product C (ID = 3) are both in one order.

What I’m trying to do is create a report which ranks which Product has appeared in the most orders - in this sample the results would be:

Product A = 3
Product B = 1
Product C = 1

I don’t know how to group by two columns correctly so that there is only one entry for each product and they’re grouped by the number of orders.

This is my current statement (which doesn’t work):

"SELECT IdProduct, COUNT(IdOrder) as numorders, ProdTitle FROM tblshopordersdetail GROUP BY IdProduct, IdOrder ORDER BY COUNT(IdOrder) DESC"  

I hope I’ve explained that ok and that someone can help.

Many thanks.

[molona]

You’re couting the field IdOrder… you can’t use it in your GROUP BY

[scim1971]

Hi,

Thanks for the reply - I didn’t know that.

I added the field IdOrder as a seperate field but I still get a list of one entry for each line. This is my amended statement:

"SELECT IdProduct, IdOrder, COUNT(IdOrder) as numorders, ProdTitle FROM tblshopordersdetail GROUP BY IdOrder, IdProduct ORDER BY COUNT(IdOrder) DESC"

and the results is:

1 x Product A
1 x Product B
1 x Product A
1 x Product C
1 x Product A

[molona]

The first thing: you need to have clear the question you want answered… so phrasing the question is important… because if you use the right words and ask the right question, it gives you the clues to know which fields you should use…

So… if you ask “In how many orders can I find product A?”, you know that you have to count the number of orders where product A was included… That is, your COUNT function will applied to your IdOrder field which represents the order itself.

Now, IdProduct and ProdTitle, in this case, should be about the same for our purpose… since IdProduct can be only linked to just one description or product name. Still, as you well thought, it is useful to have both bits of information… so the first part will be

SELECT idProduct, ProdTitle, COUNT(IdOrder) FROM tblshopordersdetail

Now, 1 idProduct = 1 ProdTitle. And both fields are included in y my SELECT so the next bit is

GROUP BY IdProduct, ProdTitle

Still, I want the product that appears in the highest number of orders possible to be at the top…

So my third bit would be

ORDER BY COUNT(IdOrder) DESC

So if put all together

SELECT idProduct, ProdTitle, COUNT(IdOrder) FROM tblshopordersdetail GROUP BY IdProduct, ProdTitle ORDER BY COUNT(IdOrder) DESC

[scim1971]

Ahhh, got it now.

I’ve removed the IdOrder from the GROUP BY clause and it now works.

Thanks for your help.

[r937]

@molona

You’re couting the field IdOrder… you can’t use it in your GROUP BY

actually, you can… but you’ll get counts of 1

the part about putting both IdProduct and ProdTitle into the GROUP BY clause since they are both in the SELECT clause – that’s standard sql (only mysql lets you get away with not having ProdTitle in the GROUP BY, although this is admittedly a tangent to this particular query)

[molona]

@r937

actually, you can… but you’ll get counts of 1

Thanks for the clarification