Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I would like to make a sum on first values or distinct values on different partitions in a QVlogic table.
For exemple my logic table contain :
C1 C2 C3
1 1 18
1 1 18
1 2 18
1 2 18
1 3 19
1 3 19
I want the sum of the distinct C3 of the partition C1,C2. The value of C3 is the same on each the partition C1,C2.
If I write sum(distinct C3) i will lost a 18 in my sum...
In SQL Oracle I could write :
SELECT SUM(C4)
FROM
(SELECT case when (RANK() OVER (PARTITION BY C1,C2 ORDER BY C3))=1 then C3 END as C4
from
(SELECT '1' as C1,'1' as C2,'18' as C3 FROM DUAL UNION
SELECT '1','1','18' FROM DUAL UNION
SELECT '1','2','18' FROM DUAL UNION
SELECT '1','2','18' FROM DUAL UNION
SELECT '1','3','19' FROM DUAL UNION
SELECT '1','3','19' FROM DUAL))
Any idea for my QV expression ?
This should work: sum(aggr(sum(distinct C3),C1,C2))
Regards,
Kiran.
It's all right, thanks a lot !