Nested Aggregates

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

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.

On Apr 27, 1:37 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:

>
> You have missed out a comma after Qty.

Yep, you're correct and that explains the first error. However, with
the comma in place, I get this:

#1111 - Invalid use of group function

I suppose I'll have to do a second select statement to get the size
information and concatenate the two results sets. It's not elegant
but is there any other way?

Thanks,
BB