Problem with aggregate field in report

[agnar]

Although, this example is contrived, I basically have a database storing customers, products and the quantity they purchased.

The thing that causes me trouble is that one purchase can contain the same product several times, with different quantities.
So what I want, is a report that sums up the quantity for each product per purchase for those purchases that are not already invoiced.

The basic SQL is something like

Code: Select all

SELECT *, SUM(pui_quantity) AS total_quantity FROM purchase
LEFT JOIN purchase_item ON pui_purchase_id = purchase_id
LEFT JOIN customer ON customer_id = pur_customer_id
WHERE pur_invoice_date IS NULL
GROUP BY customer_id, pui_product_id

My SQL statement works as intended. My problem is to get the SUM(pui_quantity) AS total_quantity into my report. As the report builder is not recognizing the fields when SQL is written manually, and I can't find a way to add the SUM field using the SQL-builder.

[admin]

agnar,

I think you will have a problem using GROUP BY when you use SELECT *

But if you include just the fields you need it might work.

SELECT customer_id, pui_product_id, pui_product_name, SUM(pui_quantity) AS total_quantity FROM purchase
LEFT JOIN purchase_item ON pui_purchase_id = purchase_id
LEFT JOIN customer ON customer_id = pur_customer_id
WHERE pur_invoice_date IS NULL
GROUP BY customer_id, pui_product_id


Steven