Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in order
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...
> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--