From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | SF Postgres <sfpug(at)postgresql(dot)org> |
Subject: | Re: Reporting: Oracle-like features? |
Date: | 2003-01-20 22:36:04 |
Message-ID: | 20030120223604.GB19976@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick Hatcher | 2003-01-20 23:55:19 | Re: Patrick's Evil Query |
Previous Message | Josh Berkus | 2003-01-20 22:17:51 | Re: Reporting: Oracle-like features? |