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

Re: Reporting: Oracle-like features?

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Reporting: Oracle-like features?
Date: 2003-01-21 16:16:08
Message-ID: web-2320743@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: sfpug
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


In response to

Responses

sfpug by date

Next:From: Dror MatalonDate: 2003-01-21 18:47:51
Subject: Re: Reporting: Oracle-like features?
Previous:From: Stephan SzaboDate: 2003-01-21 00:17:02
Subject: Re: Patrick's Evil Query

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