GROUP BY, JOIN, GROUP_CONCAT....???? Grrrrr...

[dtyson2000]

Hi mysql guys (or gals, as the case may be). Sorry to bug you with something that is probably pretty simple but...

 

I have a table for purchases (orders) that includes a column for productID and quantity. When a customer places an order that has more than one product, the order is split into two rows accounting for the productID and a quantity with each row containing the same orderID.

 

table: orders

orderID || productID || quantity
10026 || 2 || 10
10026 || 9 || 1

 

I want to produce a query that combines the orderID and the productID and quantity into one row.

 

SELECT orderID, productID, quantity FROM orders GROUP BY orderID;

This results in:

orderID || productID || quantity
10026 || 9 || 1

 

How can I produce the following result:

 

orderID || quantityPID2 || quantityPID9
10026 || 10 || 1

 

I have a basic understanding of MySQL and can certainly implement solutions as I find them in the manual but I just don't know where to start. I've thought about JOIN, SUM, GROUP_CONCAT, etc, etc... But I just can't come up with a solution.

 

Any thoughts?

 

Thanks, in advance!

[aschk]

Ok that's interesting; it looks like you're trying to create a pivot table BUT with an unknown number of potential columns (i.e. quantityPID2, quantityPID9.

 

My question to you is WHY do you require this? What's wrong with just 2 rows for the orderID 10026? What is it you plan on doing with this information?

[svivian]

If the orderID will be unique in the query (ie you're only looking up one orderID at a time) then just get all the rows without grouping and loop through them with PHP.

[dtyson2000]

Ok that's interesting; it looks like you're trying to create a pivot table BUT with an unknown number of potential columns (i.e. quantityPID2, quantityPID9.

 

My question to you is WHY do you require this? What's wrong with just 2 rows for the orderID 10026? What is it you plan on doing with this information?

 

It's to simplify an accounting procedure. We're using zen cart software and several months ago, we did the multiple row thing with the query I wrote. It wasn't a HUGE deal to do it that way. I just thought I would simplify the output. The data is subsequently put into Excel and, with multiple rows, requires quite a bit of editing (which, again, isn't a huge deal).

 

There is actually a finite number of products - I believe there are only 9. So there would really only be a potential of nine columns.

 

A "pivot table" you say? Hmm... I'll see what I can find about these... Thanks!

[aschk]

If there are only 9 products then we can probably break it down into 9 columns and use a pivot table to give you your layout.

 

par example:

SELECT orderID
      ,SUM(quantity*(1-abs(sign(productID-1)))) as productPID1
      ,SUM(quantity*(1-abs(sign(productID-2)))) as productPID2
      ,SUM(quantity*(1-abs(sign(productID-3)))) as productPID3
      ,SUM(quantity*(1-abs(sign(productID-4)))) as productPID4
      ,SUM(quantity*(1-abs(sign(productID-5)))) as productPID5
      ,SUM(quantity*(1-abs(sign(productID-6)))) as productPID6
      ,SUM(quantity*(1-abs(sign(productID-7)))) as productPID7
      ,SUM(quantity*(1-abs(sign(productID-))) as productPID8
      ,SUM(quantity*(1-abs(sign(productID-9)))) as productPID9
FROM orders
GROUP BY orderID;

[dtyson2000]

aschk......

 

That worked perfectly. Thanks a lot! I truly appreciate you taking the time to respond.

 

I also appreciate the reference to PIVOT TABLES. I'll be using them a lot more!

 

Thanks again!