Re: Reporting: Oracle-like features?

David,

> CLEAR COMPUTES
> CLEAR BREAKS
> BREAK ON item_name
> COMPUTE SUM OF sale_count*per_item ON item_name
> SELECT s.sale_time AS "When sold"
> , i.item_name AS "What sold"
> , s.sale_count AS "How many"
> , s.per_item AS "Each"
> , s.sale_count*s.per_item AS "$ale (kaching!)"
> FROM sales s, item i
> ORDER BY i.item_name, s.sale_time;

Ah. What Oracle is doing here is combining a reporting script with
SQL. One can, in fact, do the above in pure SQL, it's just awkward:

SELECT to_char(s.sale_time, 'YYYY-DD-MM') AS "When sold"
, i.item_name AS "What sold"
, s.sale_count AS "How many"
, s.per_item AS "Each"
, s.sale_count*s.per_item AS "$ale (kaching!)"
FROM sales s, item i
WHERE sales.item_id = item.item_id
UNION ALL
SELECT 'Total'
, i.item_name
, sum(s.sale_count)
, avg(s.per_item)
, sum(s.sale_count*s.per_item)
FROM sales s, item i
WHERE sales.item_id = item.item_id
ORDER BY item_name, "When sold";

This should work to give you the detail, plus a totals row just below
the detail (since "Total" will sort after numerical dates).

It's almost certainly what Oracle is doing on the back-end when it
receives your "break on" command.

-Josh Berkus