Re: SQL group totals

[CRPence <CRPbottle@xxxxxxxxx>]

  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?