-1

I have this MySQL query that returns a break down of sales of a certain Product_ID.

I'd like to expand this query to include multiple Product_IDs.

Heres what I have that gives me 1 Product_ID:

SELECT
o.Customer_ID, o.CompanyName, cg.Category, o.Order_ID,
SUM(old.LineTotal) as TOTAL,
SUM(old.qty) as PID,
COUNT(o.Order_ID) as OIDs

FROM Orders o
LEFT JOIN Order_LineDetails old ON old.Order_ID = o.Order_ID
LEFT JOIN CustomerDetails cd ON o.Customer_ID = cd.Customer_ID
LEFT JOIN _CustomerCategory cg ON cg.Category_ID = cd.Category_ID

WHERE o.IsPENDING = 0
AND o.OrderPlaceServerTime >= '2012-1-1'
AND o.OrderPlaceServerTime <= '2012-9-1'
AND o.IsVOID = 0
AND old.Product_ID = '56789' 

GROUP BY o.Customer_ID
ORDER BY TOTAL DESC
LIMIT 10

I'm trying to add somthing to do more than 1 Product_ID and get the SUMs of them as well.

This is the idea I was looking at, but obviosley this does not work.

(CASE WHEN old.Product_ID = '56789' 
        SUM(old.LineTotal) as TOTAL_56789,
        SUM(old.qty) as PID_56789)

This would repeat based on how many products I was after.

Hopfully this makes sense.

Any suggestions?

EDIT::

Here's an example of what I'd like to get back.

enter image description here

Monty
  • 1,304
  • 4
  • 19
  • 37
  • can please give sum expected result? – John Woo Sep 14 '12 at 13:49
  • Added the expected result, sorry I forgot that. – Monty Sep 14 '12 at 14:14
  • @Monty this could probably be done with prepared statements and a mock `PIVOT` but you should post the table structures of your tables and some sample data for each one so there is a clearer picture. Or Even better create a [SQL Fiddle](http://sqlfiddle.com/) with the tables and data. – Taryn Sep 14 '12 at 16:22

1 Answers1

0

Take a look at this

Basically you want pivot tables, but mysql does not support it. You can force it to some degree writing your queries dinamically in your client application. If you have more experience with stored procedures, you can try the automatic version .

Community
  • 1
  • 1
Diego
  • 682
  • 1
  • 7
  • 17