Have you tried 2 CTE's, one for the sum and one for the count?

Kind regards,

Carel Teijgeler

Op 11-11-2019 om 15:34 schreef Steve Richter:
when summarizing an order detail file on PRODCODE, how get both a summary
of ORDQTY and a count of distinct order numbers?

the end result I want is this:

PRODCODE Number orders Order qty
-------- ------------- ---------
Y98392 5 252
T89523 7 8183

I tried to do a count of a sub select where I select distinct ORDNUM on the
PRODCODE being grouped, but DB2 did not allow:

select a.prodcode, sum(a.ordqty) totqty,
count( select distinct c.ordnum
from ordDetail c
where c.prodcode = a.prodcode ) numOrders
from ordDetail a
group by a.prodcode

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.