Browse form with dynamically built columns

[absalom]

While trying to make my way with nuBuilder for two or three weeks now, I don't feel comfortable with building forms on some SQL requests: as long as I try to display subsets of existing columns, everything seems to work fine. But I don't really understand why I can't display more sophisticated requests including GROUP BY clause together with SUM, etc.
For instance, if I want to display the sum of all invoices by year, I could write a request like:

Code: Select all

SELECT year(date) as y, SUM(price) as total
  FROM invoices GROUP BY y ORDER BY y DESC

which works fine on phpMyAdmin, but as soon as I try to build a Browse form, with columns y and total, I get en empty table.

[kev1n]

Hi,

Use a SELECT FROM ... SELECT construct like this (assuming that invoices_id is your PK):

Code: Select all

SELECT y,
       total
FROM   (
SELECT invoices_id,
               Year(date) AS y,
               Sum(price) AS total
        FROM   invoices
        GROUP  BY y, invoices_id
        ORDER  BY y DESC
) T
 

Then use y and total in the Display columns.