GROUP BY clause in DB2 UDB 7.2

Hi,

Environment details:AIX 5.1,DB2 UDB 7.2 with FIX pack 11

I have a table with 140 columns containing 3 lakh records.

I am writing a query which includes all 140 columns but i need to group by only on 2 columns .

Select col1,col2,col3,col4...col140 from class_cov_code_t
group by col1,col14

The above statement raises error. (All statements in select should be include in group by clause but i require only few columns from the select list). How can this be achived .

Thanks in adv
Banu



banumaranAsked:
Who is Participating?
 
BigSchmuhConnect With a Mentor Commented:
The GROUP BY syntax needs to have every expression not in the GROUP BY clause being an aggregate functions.
This just means that when you group your records, you can first group using the primary (or unique) columns of your non-grouped tables, then add every needed columns from those non-grouped tables and the aggregate functions for the grouped tables.

Example1:
Select TblCustomer.CUST_ID, TblCustomer.CUST_NAME, Sum(TblOrder.AMOUNT) TotalAmount
From TblCustomer, TblOrder
Where TblCustomer.CUST_ID = TblOrder.CUST_ID
Group By TblCustomer.CUST_ID, TblCustomer.NAME

Example2 considering that TblCustomer.CUST_ID is unique
Select TblCustomer.CUST_ID, Max(TblCustomer.CUST_NAME) CUST_NAME, Sum(TblOrder.AMOUNT) TotalAmount
From TblCustomer, TblOrder
Where TblCustomer.CUST_ID = TblOrder.CUST_ID
Group By TblCustomer.CUST_ID

In your case, I assume the 2 columns used to group are unique and you can easily use a Max(Col3), ... Max(Col140) to avoid writing the 140 columns in your group by clause.

Hope this helps.
0
 
LowfatspreadCommented:
yes what are the other 138 columns?
 
which values do you want returned for the grouped by columns?

do you want the max or min values for them    
or do you want to sum them ?


0
 
banumaranAuthor Commented:
Hi,

Thanks you  BigSchmuh & Lowfatspread.

I was able to solve this by using this statement

Select x.col1,col2,col3,col4,col5,col6...col140 from (select col1,col14 from stage.class_cov_t )x , stage.class_cov_t y
where x.col1=y.col1 and x.col14=y.col14

here none of the columns are unique ones.

Thanks
Banu
0
 
banumaranAuthor Commented:
Hi

A small correction in the above query ,group by is missing in the above one

This is the correct query

Select x.col1,col2,col3,col4,col5,col6....x.col14...col140 from (select col1,col14 from stage.class_cov_t  group by col1,col14 )x , stage.class_cov_t y
where x.col1=y.col1 and x.col14=y.col14 order by col20

Thanks
Banu
0
All Courses

From novice to tech pro — start learning today.