Re: newbie syntax question

From: <fitzjarrell_at_cox.net>
Date: Thu, 21 Jun 2007 12:45:26 -0700
Message-ID: <1182455126.273181.138770@n2g2000hse.googlegroups.com>

On Jun 21, 1:25 pm, justsomeschm..._at_hotmail.com wrote:
> I have a syntax question.. Below is a ficticious version of what data
> I'm working with, but it gets the point of my question across.
>
> Pulling from an Orders table (and filtered for completed orders only),
> the below gives me a rowlist of all unique OrderGroups & their
> respective OrderGroup totals (which are caclulated elsewhere and
> stored):
>
> SELECT DISTINCT
> --o.OrderID --dont need
> o.OrderGroupID,
> o.OrderGroupTotal
>
> FROM Orders o
>
> WHERE o.Status = 2 --completed orders
>
> like so:
>
> OrderGroupID OrderGroupTotal
> -------------------------------------------------------------------------
> 2 1000
> 7 500
> 10 2000
>
> ...cool. But what I really want is the sum of those totals ($3500). Is
> there a way to modify the SQL to: A) return that, B) do so in a single
> column, so that my proc can do a SELECT INTO and populate a return
> parameter (eg, "r_SummedOrderGroupTotal_Completed", one for
> "r_SummedOrderGroupTotal_Pending", etc..) ?
>
> I know I could use a cursor and LOOP thru it, but I've read straight
> SQL would be better.
>
> I am on Oracle 9i and working in Toad. If I've missed any other info,
> I would be happen to provide it.
>
> Thanks,
>
> John

         SELECT DISTINCT
                   OrderGroupID,
                   OrderGroupTotal,
                   sum(OrderGroupTotal) ttl_completed
         FROM Orders
         WHERE Status = 2; --completed orders

would produce:

 OrderGroupID OrderGroupTotal TTL_COMPLETED  

---

  2                       1000                                   3500
  7                       500                                     3500
  10                      2000                                    3500

Yes, the total is the same for all records regardless of the OrderGrouptotal. You could write a 'running' total:

         SELECT DISTINCT
                   OrderGroupID,
                   OrderGroupTotal,
                   sum(OrderGroupTotal) over (partition by
OrderGroupID order by 1 range unbounded preceding) ttl_completed
         FROM Orders
         WHERE Status = 2; --completed orders

which should produce (untested with this data):

 OrderGroupID OrderGroupTotal TTL_COMPLETED  

---

  2                       1000                                   1000
  7                       500                                     1500
  10                      2000                                    3500

Modifying such a query for other stages of order 'completion' shouldn't be difficult. Also, using the WITH() syntax you could code several of these running sums and produce a report from the 'final' query:

with x as (...),
       y as (...),
       z as (...)

select ..... from x, y, z where .....;

Jonathan Lewis has some excellent examples in his book "Cost-Based Oracle Fundamentals"; I would purchase a copy and read it through, especially the section on subquery factoring.

David Fitzjarrell Received on Thu Jun 21 2007 - 14:45:26 CDT