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

Re: Reporting: Oracle-like features?

From: Sean Chittenden <sean(at)chittenden(dot)org>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Reporting: Oracle-like features?
Date: 2003-01-21 19:49:45
Message-ID: 20030121194945.GA45119@perrin.int.nxad.com (view raw or flat)
Thread:
Lists: sfpug
> > 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

In response to

sfpug by date

Next:From: pDate: 2003-01-22 14:24:52
Subject: 新年快樂....
Previous:From: Dror MatalonDate: 2003-01-21 18:47:51
Subject: Re: Reporting: Oracle-like features?

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