Re: Reporting: Oracle-like features?

Kind people,

I've been hitting my head on a wall here, and figured it's got enough
bruises for the moment.

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

Does anything attached to PostgreSQL do this kind of thing? If so,
what?

Big TIA for any hints, tips or pointers :)

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

SQL does this, albeit, clumsily. If you want to send me
what you have and what you are looking for I may be able
to help.

elein

On Monday 20 January 2003 13:50, you wrote:
> Kind people,
>
> I've been hitting my head on a wall here, and figured it's got enough
> bruises for the moment.
>
> When I'm using Oracle, I can do things like "break on foo" and
> "compute sum of bar"
>
> Does anything attached to PostgreSQL do this kind of thing? If so,
> what?
>
> Big TIA for any hints, tips or pointers :)
>
> Cheers,
> D

--
----------------------------------------------------------------------------------------
elein(at)varlena(dot)com Database Consulting www.varlena.com
I have always depended on the [QA] of strangers.

David,

> 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

?

-Josh

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

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

Very cool. And you know which row has the subtotal since it'll have the
value "Total" in the first column.

I like. :-).

On Tue, Jan 21, 2003 at 08:16:08AM -0800, Josh Berkus wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
Dror Matalon
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com

> > 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.

I do a ton of this stuff, but I've never thought about using a UNION,
I always end up doing a join on a sub-select. Would a UNION be
faster? For me, summing up millions of rows isn't exactly quick and
isn't exactly the easiest query in the world on the database. ;) -sc

--
Sean Chittenden