Home » SQL & PL/SQL » SQL & PL/SQL » SUM, DISTINCT and GROUP BY CUBE problem
SUM, DISTINCT and GROUP BY CUBE problem [message #258937] 
Mon, 13 August 2007 16:44 
YBubnov
Messages: 2 Registered: August 2007

Junior Member 


Hi all
I'm trying to perform query like CUBE in the table
SQL> select * from yubu_types
2 /
C1 C2 C3 C4
   
a z 1 100
a z 1 100
a z 11 100
b z 1 100
b y 2 200
b z 3 300
a y 1 100
a z 2 200
a z 1 100
9 rows selected.
SQL>
I want to perform GROUP BY CUBE query on C1 and C2.
SELECT c1, c2, COUNT(DISTINCT c3), SUM(DISTINCT c4)
FROM
(SELECT c1,
c2,
c3,
c4
FROM yubu_types
)
GROUP BY cube(c1, c2)
The problem is that C3 contains 2 values (1 and 11) with equal corresponding C4 value (100) but I want to SUM treat it as 2 different values.
The result I expected is
C1 C2 COUNT SUM
   
a y 1 100
a z 3 400
b y 1 200
b z 2 400
a 3 400
b 3 600
y 2 300
z 4 700
4 700




Re: SUM, DISTINCT and GROUP BY CUBE problem [message #258961 is a reply to message #258937] 
Mon, 13 August 2007 22:42 
flyboy
Messages: 1834 Registered: November 2006

Senior Member 


Quote:  I want to SUM treat it as 2 different values.

So simply calculate it in the inner query aggregating with GROUP BY c1, c2, c3; in the outer query will just calculate SUM(s4).
Choose appropriate aggregate function in the inner query to calculate data below as you want (SUM(DISTINCT c4)?)
C1 C2 C3 C4
   
a z 1 100
a z 1 100
a z 1 200
a z 11 100
...



Re: SUM, DISTINCT and GROUP BY CUBE problem [message #259111 is a reply to message #258937] 
Tue, 14 August 2007 07:42 
YBubnov
Messages: 2 Registered: August 2007

Junior Member 


2 rleishman
Quote:  Then remove the DISTINCT keyword from the SUM()

if i remove DISTINCT then SUM will count duplicate records twice
C1 C2 C3 C4
   
a z 1 100
a z 1 100 But I don't want it
2 flyboy
I'm not sure I understood what you wanted to describe.
May be I was not very clear.
In abstract matter I want to perform SUM by one column but at the same time some how define unique values by other column or set of columns.
In this case during GROUP BY CUBE in (a,z) case I wanted to calculate SUM(C4) but define different values by set of (C1, C2, C3) but then during calculation CUBE in (a, null) calculate SUM(C4) but define different values by set of (C1, C3)
In both cases (a,z) and (a,null) I should get the same COUNT(C3) and SUM(C4) because there are same set of (C3,C4)....
I'm not sure I can get required result without just calculating 4 different COUNT and SUM and then UNION them



Re: SUM, DISTINCT and GROUP BY CUBE problem [message #259222 is a reply to message #259111] 
Tue, 14 August 2007 14:16 

vamsi kasina
Messages: 2107 Registered: October 2003 Location: Riyadh, Saudi Arabia

Senior Member 


I think UNION is better.SELECT a, b, COUNT(c), SUM(d)
FROM (select c1 a,c2 b,c3 c,c4 d
from vam_test
union select c1,null,c3,c4
from vam_test
union select null,c2,c3,c4
from vam_test
union select null,null,c3,c4
from vam_test)
group by a,b; By
Vamsi



Re: SUM, DISTINCT and GROUP BY CUBE problem [message #259243 is a reply to message #258937] 
Tue, 14 August 2007 17:06 
flyboy
Messages: 1834 Registered: November 2006

Senior Member 


Quote:  during GROUP BY CUBE in (a,z) case I wanted to calculate SUM(C4) but define different values by set of (C1, C2, C3)
during calculation CUBE in (a, null) calculate SUM(C4) but define different values by set of (C1, C3)

This is the problem  different areas for distinct values in different levels of grouping.
I am afraid this cannot be done in one GROUP BY query.



Re: SUM, DISTINCT and GROUP BY CUBE problem [message #259283 is a reply to message #259243] 
Tue, 14 August 2007 23:08 
rleishman
Messages: 3727 Registered: October 2005 Location: Melbourne, Australia

Senior Member 


Remove the DISTINCT from the SUM, but add a distinct to the inner select to ignore duplicate rows
SELECT c1, c2, COUNT(DISTINCT c3), SUM(c4)
FROM
(SELECT DISTINCT c1,
c2,
c3,
c4
FROM yubu_types
)
GROUP BY cube(c1, c2)
Ross Leishman



Goto Forum:
Current Time: Wed Jan 18 10:24:30 CST 2017
Total time taken to generate the page: 0.10570 seconds
