Re: SQL group totals

I wonder if the given data might not be a little overly simplistic? My response assumes the NULL value is not allowed.

If the highest numeric value for order# & pick#, across the group, always gives the expected count, then perhaps:

<code>
-- Assuming order# always increments by one w/in the group and
-- the pick# always increments even across orders as data shows
select co#, cust#, po#, max(order#), max(pick#)
from thefile
group by co#, cust#, po#
<code>

If the maximum value is not always ensured to match the desired count, because the values in order# need only be distinct to influence the count, yet the pick# values remain unique across all orders in a group as shown, then perhaps:

<code>
select co#, cust#, po#, count(distinct order#), count(distinct pick#)
from thefile
group by co#, cust#, po#
</code>

If the pick# rolls over to one, on each new order#, then perhaps:

<code>
-- Assuming order# always increments by one w/in the group
-- but that the pick# restarts at one on a new order
select co#, cust#, po#, max(order#), count(*)
from thefile
group by co#, cust#, po#
</code>

With no assumptions of what the _values_ might be in rows for either order# or pick# [i.e. an actual row count versus values count], then perhaps:

<code>
with
SumOrd (co#, cust#, po#, order#, pickcount) as
select co#, cust#, po#, order#, count(*)
from thefile
group by co#, cust#, po#, order#
select co#, cust#, po#, count(*), sum(pickcount)
from SumOrd
group by co#, cust#, po#
</code>

Regards, Chuck

Craig Jacobsen wrote:

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?