Problem with aggregate field in report

Questions related to nuBuilder Forte Reports and the Report Builder.

Problem with aggregate field in report

Postby agnar » Wed Jun 06, 2018 11:52 pm

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.
agnar
 
Posts: 37
Joined: Mon Apr 22, 2013 6:28 am

Re: Problem with aggregate field in report

Postby admin » Wed Jul 11, 2018 11:04 am

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
admin
Site Admin
 
Posts: 3108
Joined: Mon Jun 15, 2009 9:53 am


Return to Reports



cron