Group By + Sum 1

[quietstormtw]

Hi All,

I've run into an issue that I just can't phathom how to go about getting done...

I have listed the information from the table below. What I am looking to do is group the information by AccountNo, BeginYr...then sum by the Rate...listing the Rep and End Yr columns:

AccountNo Rep BeginYr EndYr Rate

12345 1 1 1 50
12345 1 2 99 50
12345 2 1 1 50
12345 2 2 99 50

How would I go about this?

Thanks in advance!!

 

[SantaMufasa]

Stormy,

To ensure that we solve the correct problem, could you please post a visual representation of how you want the above source data to appear on output?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[quietstormtw]

Hi Dave,

The output that I am looking for should be something like

AccounNo BeginYr EndYr Sum(Rate)
12345 1 1 100
12345 2 99 100

I suppose by adding the Rep column, it would mess up things a bit?

 

[oradba101]

Good afternoon,

You could try the CUBE or ROLLUP options available with GROUP BY?

Regards,

William Chadbourne
Oracle DBA

 

[quietstormtw]

Hi Bill,

I have 3 SQL reference books on my desk right now...and none mention anything about Cube or Rollup options...

Could you please direct to a site that would have this information?

 

[SantaMufasa]

...listing the Rep and End Yr columns...

I see the End Yr column in your output, but I don't see the Rep column...How does it figure in?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[quietstormtw]

Hi Dave,

The Rep and EndYr columns are not being grouped or calculated...so I would eliminate them?

Or could the still be listed, without being grouped?

 

[SantaMufasa]

>>> The Rep and EndYr columns are not being grouped or calculated...so I would eliminate them?

If they are not business necessary to your output, then don't even refer to them.

>>> Or could the still be listed, without being grouped?

If those data are business necessary but you don't want to group by them, then we must "trick" Oracle into listing them, but that is why I needed to know what you need for output before I assert a solution for you.

Please let us know whether or not those columns are central to your solution.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[quietstormtw]

The columns are necessary to the output...but not to the calculations. How would you go about 'tricking' Oracle to have the data listed.

I was just looking for a little assistance...I'm sorry if I upset you.

 

[SantaMufasa]

Absolutely no upset, QS. (That's the problem with text-based forums...you can't see my "body language", which would indicate that I'm fine with your question...I just need to clearly understand your needs/wishes before my proposing a solution.)

So, to clarify, could you please confirm whether or not you want the data to group, break by, and sum(rate) by:

distinct BeginYR+EndYr combinations within AccountNo?

...and how does "Rep" figure into things? (e.g., Reps "1" and "2" both "contribute" to the Rate summing, yet how do you want them to appear on output?

If you want "Rep" values to appear, please amend and re-post (from above) your output specifications.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[quietstormtw]

Thanks Dave - to move on...

I've been working with the GROUP BY RollUp function and listed my code below:

SELECT
accountno,
beginyr,
sum(rate) AgtRate
FROM
table1
GROUP BY ROLLUP (accountno, beginyr)
ORDER BY 1

The output that I receive is good, but there seems a be an total line added, which should be pretty easy to get rid...if I knew how it got there:

Output

AccountNo BeginYr AgtRate
12345 1 100
12345 2 100
12345 200
54321 1 100
54321 2 100
54321 200

Where did the extra line total come from? And how can I get rid of it?

 

[SantaMufasa]

QS,

The extraneous "total" lines result from your "ROLLUP" specification. If you remove that, then it eliminates the extra total lines:

SELECT
    accountno,
    beginyr,
    sum(rate) AgtRate
FROM
    table1
GROUP BY (accountno, beginyr)
ORDER BY 1
/

ACCOUNTNO    BEGINYR    AGTRATE
--------- ---------- ----------
    12345          1        100
    12345          2        100
    54321          1        100
    54321          2        100

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[quietstormtw]

Well Dave,

It seems as if I've made a big to-do out of nothing here.

Thanks for your time & efforts...