RE: SQL group totals

Craig,

How about something like this (untested).

With UniqueOrders as (select co#, cust#, po#, order# from orders group
by co#, cust#, po#),
With OrderCount as Select co#, cust#, po#, count(*) as PO_Orders from
UniqueOrders group by co#, cust#, po#),
With PickCount as (select co#, cust#, po#, count(*) as PO_Picks from
orders group by co#, cust#, po#)

Select 'A', * from orders
Union all
Select 'B', t1.co#, t1.cust#, t1.po#, PO_Orders, PO_Picks
Order by 1, 2, 3, 4, 5

There might be an easier way but this would be my first pass at it.

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Craig Jacobsen
Sent: Monday, June 30, 2008 2:17 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL group totals

SQL gurus,

I need to get 2 group totals.
I have a table that contains Co#, Cust#, PO#, Order#, Pick#.
There could be multiple Order#s and multiple Pick#s.
(One PO# could generate more than one internal Order# and each Order#
could generate more than one pick#.

The warehouse manager would like to see a report by detail (no problem):
Co# Cust# PO# Order# Pick#
100 101 a1 1 1
100 101 a1 1 2
100 101 a1 1 3
100 101 a1 2 4
100 101 a1 2 5

And a report summary:
Co# Cust# PO# #Orders #Picks
100 101 a1 2 5

I could write an RPG program and use level breaks which would be very
easy for me. I am worried about maintaining the program when I'm not
around and not everyone here understands the RPG cycle.

How would I do this with SQL?

TIA,

Craig

Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.