Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

decode with group by 2

Status
Not open for further replies.

raddycal

Programmer
Nov 27, 2006
16
US
I have a select stmt. with decode and a sum. when I put the decode in the group by clause, it says "not a group by expression". when I put the columns from the decode in the group by clause, it says "not a group by expression".
when I put the alias for the decoded value in the group by
clause, it says "not a group by expression". how do I fix this?

thanks

RH
 
Take whatever field is in the decode and add to the group by.

Or do a Max or sum on the Decode statement

max(decode.....) as YourColName

Ian
 

-- Or --
Show us your query.
[3eyes]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Raddycal,

Absent a sample of your own code, here is one that (from your description) does what you want:
Code:
SELECT DECODE(DEPT_ID,41,'Operations','31','Sales') Department,sum(salary) Salaries
  from s_emp
 where DECODE(DEPT_ID,41,'Operations','31','Sales') is not null
 group by DECODE(DEPT_ID,41,'Operations','31','Sales')
 order by Department;

DEPARTMENT                       SALARIES
------------------------------ ----------
Operations                           4990
Sales                                2800
You cannot refer to a column alias in a WHERE clause, a GROUP BY clause, or a HAVING clause. The only clause in which you may refer to a column alias is in the ORDER BY clause.

Let us know if this gives you the information you needed to resolve your issue.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top