decode with group by 2

[raddycal]

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

 

[IanWaterman]

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

 

[LKBrwnDBA]

-- Or --
Show us your query.



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

 

[SantaMufasa]

Raddycal,

Absent a sample of your own code, here is one that (from your description) does what you want:

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.

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.”

 

[raddycal]

thanks everyone. my query is working now!

RH