MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » July 2008

RE: SQL group totals



fixed

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?





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact