billbois at gmail dot com wrote:
> I'm not sure if this is possible but my attempts have failed so far.
> I'm trying to get a single resultset that would look like this:
>
> -------------------------------------------------------------------------
>| ProductID | ProductCode | Price | Qty |
> Details |
> -------------------------------------------------------------------------
>| 38 | Barn T-shirt | 20.00 | 102 | 2XL=27, L=30, M=20,
>| XL=25 | 39 | Map T-shirt | 18.00 | 37 | L=9, M=8, XL=10,
>| 2XL=10 | 40 | Army T-shirt | 15.00 | 83 | 2XL=33, L=21,
>| M=16,
> XL=13 |
> -------------------------------------------------------------------------
>
> I need to join three tables (Product, Transaction and
> TransactionDetail) to show how many of each Product have been sold.
> The first three columns show data about each product. The fourth
> column shows the SUM of how many have sold.
>
> The fifth column is giving me trouble. I want it to show how many of
> each size of the Product have sold. I can get it to show the various
> sizes using the GROUP_CONCAT function, but get an error when I try
> nesting the SUM function inside it to show how many of each size have
> sold.
>
> The query I'm trying looks something like this. For simplicity, I've
> removed out the WHERE clause that filters by date and other
> parameters.
>
> SELECT p.ProductID,
> p.ProductCode,
> p.Price,
> IFNULL(SUM(td.QTY), 0) AS Qty
> GROUP_CONCAT(DISTINCT CONCAT(td.ProductDetails, '=', SUM(td.Qty))) AS
> Details
> FROM Product p
> LEFT JOIN TransactionDetail td ON p.ProductID = td.ProductID
> LEFT JOIN Transaction t ON td.TransactionID = t.TransactionID
> GROUP BY p.ProductID, p.ProductCode, p.Price
>
> The error is:
>
> #1064 - You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'GROUP_CONCAT(DISTINCT CONCAT(td.ProductDetails, '=',
> SUM(td.Qty))) AS Details
> F' at line 5
>
> Is there any way to get that resultset without running a second query
> inside a loop? Am I asking too much?
>
> Thanks,
> BB

You have missed out a comma after Qty.