AGGREGATION

Database
Enthusiast

AGGREGATION

HI !!

I am facing a very unusual situation . Query is  doing agrregate sum function on one of the amount columns 

sel 

col1,

col2,

SUM(AMT_COLUMN1) as sum1,

--SUM(AMT_COL2)as sum2

from TABLE

group by 

col1,

col2

having sum1>0

Now when I am trying to comment out the second column to fetch the sum1 value greater than 0 it is throwing the error that non aggregated values must be part of group by error and when I add it to the group by list . It is actually working fine.

What can be possible cause? Any help !! 

5 REPLIES
Enthusiast

Re: AGGREGATION

Bhalla,

you can check it once again. Maybe you miss out the comma(s) etc. Else you paste here your create table., Sample data and what you select and what you get. Then we can help.

Cheers,

Enthusiast

Re: AGGREGATION

Thanks Raja!!

actually the query is fetching almost 74K records . anyways I wanted to know just one thing.

if the (AMT_COLUMN1) is having unique values for the rest of the combination .

COL1             COL2             AMT COL1

A                        B                     10

C                        D                      11

Will it still be possible to consider the result of sum(AMTCOL1) as an aggregated output??

Senior Apprentice

Re: AGGREGATION

What are you trying to achieve?

Any column which is not part of the GROUP BY must be used within an aggregate function. 

Without showing the actual query it's hard to tell what's wrong with it.

Enthusiast

Re: AGGREGATION

I am not sure about your thought.I think what you mean to say: If the column values  to be grouped by are unique , then no need to do summation. Then yes.

I suggest you paste your query, sample data and expected results, for faster response.

Cheers,

Enthusiast

Re: AGGREGATION

Thx Dieter and Raja

Yes the case mentioned by Raja might be the issue the point is I am new into my support system and got one cognos query . i have a doubt that though they need to do the sum and they are doing it based on certain colunmns but eventually its ending up being a single row for the summation hence might be the issue. Its impossible for me to post the data set durng working hrs. I lltry over weekend if it doesnt get fix

Thanks for the hellp so far