Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: group by problem


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 4 - Last Post: Nov 27, 2014 3:38 PM Last Post By: John Birch
John Birch

Posts: 135
Registered: 8/23/02
group by problem  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 27, 2014 5:41 AM
I have this select SQL

select key_ID, (field2||' '||field3) as partition, sum(field4 * field5) as TOTAL1
from VW_REP_SUM_PARTS
group by key_ID, partition


but I get error unknown column partition, any idea ?
steve leonard

Posts: 80
Registered: 2/17/09
Re: group by problem  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 27, 2014 6:54 AM   in response to: John Birch in response to: John Birch
I have had this same problem, and the SQL works for other databases, but not for Interbase.

All I could do was to not use the "as" name in the group by statement but instead I used the field name and I found it worked, and using the "as" statement displayed as the column name..

So in this case "select key_id, field3 as partition... from VW_REP_SUM_PARTS"
"group by key_ID, field3 " might work. Sorry I don't have a better answer but that is the only way I could use the "as" statement eith a "group by"

John Birch wrote:
I have this select SQL

select key_ID, (field2||' '||field3) as partition, sum(field4 * field5) as TOTAL1
from VW_REP_SUM_PARTS
group by key_ID, partition


but I get error unknown column partition, any idea ?
John Birch

Posts: 135
Registered: 8/23/02
Re: group by problem  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 27, 2014 8:25 AM   in response to: steve leonard in response to: steve leonard
thanks but I have two fields
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: group by problem  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 27, 2014 10:28 AM   in response to: John Birch in response to: John Birch
John Birch wrote:
I have this select SQL

select key_ID, (field2||' '||field3) as partition, sum(field4 * field5) as TOTAL1
from VW_REP_SUM_PARTS
group by key_ID, partition


but I get error unknown column partition, any idea ?

Group by columns must be actual physical columns in a table. One way around
that is to create a computed by column for your partition and then use that
computed by column.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
John Birch

Posts: 135
Registered: 8/23/02
Re: group by problem  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 27, 2014 3:38 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Ok thanks Jeff
As I come from Firebird I found it much more flexible than InterBase in SQL.
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02