Thread: Using Sum() in self-join

1. Registered User
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. Registered User
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. Registered User
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. Registered User
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
•