Re: SQL Sum(Distinct())

Forgot to add ORDAM to the group by:
with OrderSummary as (select h.ONRCU, h.ORDAM,
sum(d.ITNSA) as SumItmAmt
from ORHDR H join ORDTL D using (ONRCU)
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO
group by h.ONRCU, h.ORDAM)
select sum(ORDAM), sum(SumItmAmt) into :ORDAM, :ITNSA
from OrderSummary

Note that the MAX() function used by Michael is a alternative to
putting ORDAM in the group by...

However, I prefer not to use that technique, as you aren't really
looking for the max. Assuming ONRCU is unique in the header, grouping
by ONRCU, ORDAM is still unique.

Charles

On Mon, Jun 21, 2010 at 11:09 AM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

Jeff,

Using aggregate functions across different join levels of data is
always doing to be a problem...

You could use a Common Table Expression to bring the data to the same level..

with Hdr as (select CUSNR, ROUTE, STOPX, SHPDTISO
                   SUM(ORDAM) as SumOrdAmt
                  from ORHDR
                  where CUSNR = :CUSNR and
                           ROUTE = :ROUTE and
                           STOPX = :STOPX and
                         SHPDTISO = :SHPDTISO)
, dtl as (select CUSNR, ROUTE, STOPX, SHPDTISO
                   SUM(ITNSA) as SumItmAmt
                  from ORDTL
                  where CUSNR = :CUSNR and
                           ROUTE = :ROUTE and
                           STOPX = :STOPX and
                         SHPDTISO = :SHPDTISO)
 select SumOrdAmt, SumItmAmt into :ORDAM, :ITNSA
 from hdr join dtl using(CUSNR, ROUTE, STOPX, SHPDTISO)


An alternative format that might be easier to understand

with OrderSummary as (select h.ONRCU, h.ORDAM,
                                            sum(d.ITNSA) as SumItmAmt
                                   from ORHDR H join ORDTL D using (ONRCU)
                                   Where h.CUSNR = :CUSNR and
                                            h.ROUTE = :ROUTE and
                                            h.STOPX = :STOPX and
                                            h.SHPDTISO = :SHPDTISO
                                    group by h.onrcu)
select sum(ORDAM), sum(SumItmAmt) into :ORDAM, :ITNSA
from OrderSummary

HTH,
Charles


On Mon, Jun 21, 2010 at 10:32 AM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx> wrote:

Been a while since I've asked an SQL question, so I'm due.  :)

I will soon, I think, have a need within an RPG program to get a couple of
pieces of summary information from an order header/detail pair of files.
One piece from the header file and one piece from the detail file.  I can
get the 2 pieces separately like this:

        Exec SQL
          Select SUM(ORDAM)
            Into :ORDAM
            From ORHDR
            Where CUSNR = :CUSNR and
                  ROUTE = :ROUTE and
                  STOPX = :STOPX and
                  SHPDTISO = :SHPDTISO;

        Exec SQL
          Select SUM(d.ITNSA)
            Into :ITNSA,
            From ORDTL d Join ORHDR h on d.ONRCU = h.ONRCU
            Where h.CUSNR = :CUSNR and
                  h.ROUTE = :ROUTE and
                  h.STOPX = :STOPX and
                  h.SHPDTISO = :SHPDTISO;


I wondered if I could get the 2 pieces with a single statement, so I tried
this:

        Exec SQL
          Select SUM(d.ITNSA),
                 SUM(h.ORDAM)
            Into :ITNSA,
                 :ORDAM
            From ORDTL d Join ORHDR h on d.ONRCU = h.ONRCU
            Where h.CUSNR = :CUSNR and
                  h.ROUTE = :ROUTE and
                  h.STOPX = :STOPX and
                  h.SHPDTISO = :SHPDTISO;

It executed, but the RPG field ORDAM was waAAAyyy off because field h.ORDAM
from the header file was summed for each record in the detail file.  I
understand why that is and it makes sense.  I did some googling and came up
with this, using Distinct:

        Exec SQL
          Select SUM(d.ITNSA),
                 SUM(Distinct(h.ORDAM))
            Into :ITNSA,
                 :ORDAM
            From ORDTL d Join ORHDR h on d.ONRCU = h.ONRCU
            Where h.CUSNR = :CUSNR and
                  h.ROUTE = :ROUTE and
                  h.STOPX = :STOPX and
                  h.SHPDTISO = :SHPDTISO;

That seemed to me like it would have a problem, because 2 selected order
header records COULD have the same h.ORDAM value.  Won't happen often, but
it can and does happen sometimes.  If I understand Distinct right, it would
only include one of them in the Sum function.  So I did some testing and
found that to be true.

So is there a way to do what I want in one statement?  The flip side of that
is, if the statement is very complex, I would rather do it in 2 statements
because I believe in KISS.

Thanks.


--
Jeff Crosby
VP Information Systems
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
www.dilgardfoods.com

The opinions expressed are my own and not necessarily the opinion of my
company.  Unless I say so.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.