Skip site navigation (1) Skip section navigation (2)

Re: Reporting: Oracle-like features?

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 (view raw or flat)
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

In response to

Responses

sfpug by date

Next:From: Patrick HatcherDate: 2003-01-20 23:55:19
Subject: Re: Patrick's Evil Query
Previous:From: Josh BerkusDate: 2003-01-20 22:17:51
Subject: Re: Reporting: Oracle-like features?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group