Re: Reporting: Oracle-like features?

On Mon, Jan 20, 2003 at 02:17:51PM -0800, Josh Berkus wrote:
> David,

Sorry I couldn't make the last meeting.

> > When I'm using Oracle, I can do things like "break on foo" and
> > "compute sum of bar"

> Um, like:
>
> SELECT foo, sum(bar) as bar_sum
> FROM tblfud
> GROUP BY foo
> ORDER BY foo
>
> ?

Unfortunately not.

Example tables:

CREATE TABLE item (
item_id SERIAL NOT NULL PRIMARY KEY
, item_name VARCHAR(64) NOT NULL
, item_desc TEXT
, UNIQUE(item_name)
);

CREATE TABLE sales (
sale_id SERIAL NOT NULL PRIMARY KEY
, item_id INTEGER NOT NULL REFERENCES item(item_id)
, sale_count INTEGER DEFAULT 1 NOT NULL CHECK (sale_count > 0)
, per_item INTEGER NOT NULL CHECK(per_item > 0) -- pennies
, sale_time DATETIME DEFAULT now() NOT NULL
);

Oracle-style report setup:

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;

This gives subtotals (as your query did) along with the actual sales.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778