Need clarification on Group by



IBM's flagship relational database management system

Need clarification on Group by

Postby Raja190 » Wed Jun 29, 2016 9:35 pm

Select A.ID, count(B.count) as counter
from A,B
where A.ID=B.ID
group by a.ID;



The above sql just works fine, but if I add any column from table A it fails with below error.

I believe the group by can't be made if I include one more column so its failing. Could some please clarify?


DSNT408I SQLCODE = -122, ERROR: COLUMN OR EXPRESSION IN THE SELECT LIST IS
NOT VALID
DSNT418I SQLSTATE = 42803 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOGB SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -200 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF38' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Raja190
 
Posts: 36
Joined: Mon Dec 14, 2015 8:13 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need clarification on Group by

Postby Robert Sample » Thu Jun 30, 2016 12:20 am

You didn't want to post the code in error instead of what is working? How can anybody possibly help you without you posting what you tried that didn't work?
Robert Sample
Global moderator
 
Posts: 3720
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times

Re: Need clarification on Group by

Postby Raja190 » Thu Jun 30, 2016 1:09 pm

Select A.ID,A.Name, count(B.count) as counter
from A,B
where A.ID=B.ID
group by a.ID;

 



When Name from table A is included in the select list, it through below error.

DSNT408I SQLCODE = -122, ERROR: COLUMN OR EXPRESSION IN THE SELECT LIST IS
NOT VALID
DSNT418I SQLSTATE = 42803 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOGB SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -200 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF38' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
Raja190
 
Posts: 36
Joined: Mon Dec 14, 2015 8:13 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need clarification on Group by

Postby NicC » Thu Jun 30, 2016 11:49 pm

it through below error.

This makes no senese - unless you meant 'threw' (past tesnse of 'to throw') in which case you are wrong. Errors are not thrwon on a z/OS system: they occur, are given, result in and several other ways - but NOT thrown.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Need clarification on Group by

Postby Robert Sample » Fri Jul 01, 2016 3:26 am

The Troubleshooting for DB2 manual says


-122
COLUMN OR EXPRESSION IN THE SELECT LIST IS NOT VALID
Explanation

The SELECT statement contains one of these errors:

The statement contains a column name and an aggregate function in the SELECT clause, but no GROUP BY clause.
A column name is contained in the SELECT clause (possibly within a scalar function) but not in the GROUP BY clause. Grouping expressions can be used in a SELECT list. A grouping expression specifies only one value for each group. A grouping expression that is specified in this context must exactly match a grouping expression that is specified in the GROUP BY clause, except that blanks are not significant.
Attention: A HAVING clause specified without a GROUP BY clause implies a GROUP BY with no columns. Thus, no column names are allowed in the SELECT clause.
An expression is specified in the SELECT clause but not in the GROUP BY clause.
A sort-key-expression was specified in the ORDER BY clause, the result table contains grouped data, but the select-clause and ORDER BY clause contain a mixture of grouped data and non-grouped data. Grouping expressions can be used in a sort-key-expression of an ORDER BY clause. A grouping expression specifies only one value for each group. A grouping expression that is specified in this context must exactly match a grouping expression that is specified in the GROUP BY clause, except that blanks are not significant.

System action

The statement cannot be processed.
Programmer response

You can correct the statement by:

Including the columns or expressions in the GROUP BY clause that are in the SELECT clause, or
Removing the columns or expressions from the SELECT clause.

SQLSTATE

42803
Robert Sample
Global moderator
 
Posts: 3720
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post