Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2006
    Posts
    1

    Unanswered: SQL Question counting orderlines

    Hello please can anyone help me with the following issue:\

    I have trouble to get this statement working, I have two tables order headers and order lines. They are joined with the ordernumber. The table orderlines contains multiple records with the same ordernumber and each line has a totalfield

    I need to get 1 record per order with the total of all of the orderlines.
    The field [004].dbo.orsrg.esr_aantal contains the total per orderline
    orkrg.ordernr is the ordernumber from the header and orsrg.ordernr is the ordernumber per line.

    This is my querie so far:

    SELECT [004].dbo.orkrg.ordernr, [004].dbo.orkrg.orddat, [004].dbo.orsrg.aant_fakt, [004].dbo.orsrg.artcode, [004].dbo.orkrg.debnr, [004].dbo.orkrg.levwijze,

    [004].dbo.orkrg.docnumber, [004].dbo.orkrg.ord_debtor_name, [004].dbo.orkrg.refer, [004].dbo.orsrg.afldat, [004].dbo.orsrg.oms45,

    [004].dbo.orsrg.esr_aantal

    FROM [004].dbo.orkrg INNER JOIN

    [004].dbo.orsrg ON [004].dbo.orkrg.ordernr = [004].dbo.orsrg.ordernr

    WHERE (orkrg.orddat > CONVERT(DATETIME, '2005-11-01', 102)) AND (orsrg.artcode <> '84110014') AND (orsrg.artcode <> '84100003') AND

    (orsrg.artcode <> '84400000') AND (orsrg.artcode <> '84400003') AND (orsrg.artcode <> '84550000') AND (orsrg.artcode <> '84800000') AND

    (orsrg.artcode <> '8500000') AND (orsrg.artcode <> '8590') AND (orsrg.artcode <> '8591') AND (orsrg.artcode <> '8592') AND

    (orsrg.artcode <> '86000000') AND (orsrg.artcode <> '86100000') AND (orsrg.artcode <> '86200000')

    GROUP BY [004].dbo.orkrg.ordernr, [004].dbo.orkrg.orddat, [004].dbo.orsrg.aant_fakt, [004].dbo.orsrg.artcode, [004].dbo.orkrg.debnr, [004].dbo.orkrg.levwijze,

    [004].dbo.orkrg.docnumber, [004].dbo.orkrg.ord_debtor_name, [004].dbo.orkrg.refer, [004].dbo.orsrg.ordernr, [004].dbo.orsrg.afldat,

    [004].dbo.orsrg.oms45, [004].dbo.orsrg.esr_aantal

    Can anyone please help me?

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Just replace orsrg.esr_aantal by SUM(orsrg.esr_aantal) (in the SELECT clause), and remove orsrg.esr_aantal at the very end (from the GROUP BY).
    Last edited by Peter.Vanroose; 01-20-06 at 07:26.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •