From: | Dror Matalon <dror(at)zapatec(dot)com> |
---|---|
To: | SF Postgres <sfpug(at)postgresql(dot)org> |
Subject: | Re: Reporting: Oracle-like features? |
Date: | 2003-01-21 18:47:51 |
Message-ID: | 20030121184751.GB48028@rlx11.zapatec.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Chittenden | 2003-01-21 19:49:45 | Re: Reporting: Oracle-like features? |
Previous Message | Josh Berkus | 2003-01-21 16:16:08 | Re: Reporting: Oracle-like features? |