Re: newbie syntax question

<justsomeschmuck_at_hotmail.com> wrote in message news:1182450359.657336.184960_at_o61g2000hsh.googlegroups.com...
>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
>

Distinct may not be needed.

Try the following to get a grand total as a repeating value in the last column:

select OrderGroupID, OrderGroupTotal, sum(OrderGroupTotal) over () from orders o
where o.status=2; Received on Thu Jun 21 2007 - 14:51:27 CDT