Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2001
    Posts
    2

    Unanswered: Using Sum() in self-join

    Hi folks,

    I have the following problem :

    Table Article

    Article....Qual..Amount
    4711.....1.......1,000
    4711.....1.......1,000
    4711.....2.......2,000
    4711.....2.......2,000
    4712.....3.......3,000

    The result should be :

    Article.......Q1.......Q2.......Q3
    4711.........2.........4
    4712................................3

    How can I do that ?
    I have tried it with a sum() on a self-join like :

    SELECT t."Article", SUM(t1."Amount") , SUM(t2."Amount"), SUM(t3."Amount")
    FROM "Test" t
    left outer join "Test" t1 on (t."Article" = t1."Article" AND t1."Qual"=1)
    left outer join "Test" t2 on (t."Article" = t2."Article" AND t2."Qual"=2)
    left outer join "Test" t3 on (t."Articlel" = t3."Article" AND t3."Qual"=3)
    group by t."Article"

    with the result

    Article....Q1....... Q2.........Q3
    4711.....16,000..32,000
    4712..............................3,000

    It seems that it works just on article 4712 where it is just one record.

    Does anyone have any hint for me ?

    Gruss
    JT

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189
    Hello,

    what do you think about

    Select artile, count(article), sum(amount) from table
    group by article

    or somethink like that ...

    Hope this help ?

    Greetings
    Manfred Peter
    (Alligator Company)
    http://www.alligatorsql.com

  3. #3
    Join Date
    Aug 2001
    Posts
    2
    Hi,

    thanks for your answer, but that won't work.
    The problem is, that I must have 3 Sum-columns from Row-Data.
    The field Qual can be 1, 2 or 3. And I need these Sumaries in one line, like "article Sum1 Sum2 Sum3". I can't use Temp-Tables or anything else.

    cu
    JT

  4. #4
    Join Date
    Jan 2002
    Location
    Toronto
    Posts
    21
    Try this:

    select article,
    sum(decode(qual,1,Amount,null)) Q1,
    sum(decode(qual,2,Amount,null)) Q2,
    sum(decode(qual,3,Amount,null)) Q3
    from your_table_goes_here
    group by article

    This should work if you have a limited number of Qual. If not, ...

    Let me know.
    Edward

Posting Permissions

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