All Forums Database
bhalla 6 posts Joined 11/11
16 Jan 2014
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 !! 
 
 

hbhalla
Raja_KT 1246 posts Joined 07/09
16 Jan 2014

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,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

bhalla 6 posts Joined 11/11
16 Jan 2014

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??
 
 

hbhalla

dnoeth 4628 posts Joined 11/04
17 Jan 2014

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.

Dieter

Raja_KT 1246 posts Joined 07/09
17 Jan 2014

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,

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

bhalla 6 posts Joined 11/11
17 Jan 2014

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

hbhalla

You must sign in to leave a comment.