RE: SQL group totals

["Craig Jacobsen" <CraigJacobsen@xxxxxxxxxxx>]

Thank you to everyone.

The count(distinct order#), count(distinct pick#) will work.

The last order# and pick# are incremented by 1 from a table by company when
orders and picks are run.

No null values.

Thanks again,

Craig


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, June 30, 2008 5:38 PM
To: midrange-l@xxxxxxxxxxxx
Subject: 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?