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 Go to next message
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 #258960 is a reply to message #258937] Mon, 13 August 2007 22:37 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Then remove the DISTINCT keyword from the SUM()

Ross Leishman
Re: SUM, DISTINCT and GROUP BY CUBE problem [message #258961 is a reply to message #258937] Mon, 13 August 2007 22:42 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Missing Lines In A Spool File...
Next Topic: Help w/ execute immediate
Goto Forum:
  


Current Time: Fri Dec 09 06:15:38 CST 2016

Total time taken to generate the page: 0.09753 seconds