PDA

View Full Version : Using Sum() in self-join

stalle
12-19-01, 04:19
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
alligatorsql.com
12-19-01, 05:53
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
stalle
12-19-01, 06:01
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
EdwardP
01-02-02, 18:22
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