Home » SQL & PL/SQL » SQL & PL/SQL » problem with 'not a single group function'
icon9.gif  problem with 'not a single group function' [message #247140] Sun, 24 June 2007 12:22 Go to next message
kandhola
Messages: 6
Registered: June 2007
Junior Member

Hi,

I have been trying to resolve a problem for hours and I cant seem to get it right . I am trying to set up a decode query which returns a count for the no of employees who earn less than 1000.
The query is pasted below. The first part works ok in isolation (return the no of employees in each dept) however the second part
(return count of emps earning <1000) causes probs. the following error msg is returned:
----------------------------------------------------------------
select d.name,decode(d.name,'ACCOUNTING',count(e.department_id),
*
ERROR at line 1:
ORA-00937: not a single-group group function
-----------------------------------------------------------------
d.name is in the group by clause so i dont understand what's going wrong. the query is below, I would appreciate any assistance, thanks.

select d.name,decode(d.name,'ACCOUNTING',count(e.department_id),
'OPERATIONS',count(e.department_id),
'RESEARCH',count(e.department_id),
'SALES',count(e.department_id),null),
count(decode(GREATEST(1000,sum(decode(d.name,'ACCOUNTING',e.salary,null))),1000, e.salary,null)) ABC


from department d join employee e
on d.department_id = e.department_id
group by d.name
having count(e.department_id) <=5;
Re: problem with 'not a single group function' [message #247142 is a reply to message #247140] Sun, 24 June 2007 12:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You did a poor job at reading & FOLLOWING the posting guidelines as enumerated in the #1 STICKY post at the top of this forum.

try
group by d.name,decode(d.name,'ACCOUNTING',count(e.department_id),
instead of what you have/had
Re: problem with 'not a single group function' [message #247143 is a reply to message #247140] Sun, 24 June 2007 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the purpose of SUM in line before FROM?
You have 2 levels of grouping function here, this is the reason of the error message.

Btw:
Read and follow How to format your posts
Break your lines to max 80-100 characters when you format.
Always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Sun, 24 June 2007 12:33]

Report message to a moderator

Re: problem with 'not a single group function' [message #247144 is a reply to message #247142] Sun, 24 June 2007 12:41 Go to previous messageGo to next message
kandhola
Messages: 6
Registered: June 2007
Junior Member

apologies, I should have read it before posting.

I tried your solution, but it returns an error
stating 'group function not allowed here' in relation to the
count present within the decode.

cheers.
Re: problem with 'not a single group function' [message #247145 is a reply to message #247143] Sun, 24 June 2007 12:46 Go to previous messageGo to next message
kandhola
Messages: 6
Registered: June 2007
Junior Member

hi,

apologies for the poor formatting earlier.

The SUM is the aggregate the total of the e.salary column
as it is this number which is to be compared to 1000. if the SUM is lower than 1000 then e.salary should be subjected to the count.

hence i would like it to return the count of empoyees in accounting earning less than 1000.

cheers.
Re: problem with 'not a single group function' [message #247146 is a reply to message #247145] Sun, 24 June 2007 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want to sum all salaries in a department and check it against 1000?
This is not your requirement.

Regards
Michel
Re: problem with 'not a single group function' [message #247150 is a reply to message #247146] Sun, 24 June 2007 13:02 Go to previous messageGo to next message
kandhola
Messages: 6
Registered: June 2007
Junior Member

hi,

I would like a count of the number of employees who earn less than a 1000 in a department. I tried to do this by using SUM in decode to test against 1000. and then returning the number of rows which equate to being true.
Re: problem with 'not a single group function' [message #247152 is a reply to message #247150] Sun, 24 June 2007 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So back to my question. What do you think the SUM does in your query? What does it sum?

Regards
Michel
icon1.gif  Re: problem with 'not a single group function' [message #247153 is a reply to message #247152] Sun, 24 June 2007 13:34 Go to previous messageGo to next message
kandhola
Messages: 6
Registered: June 2007
Junior Member

hi

in this case i would like to SUM the e.salary column where the d.name column = 'ACCOUNTING' to test against 1000.

if 1000 is greater than the value returned by SUM then e.salary should be passed to count. else if the sum expression is greater then nothing (null) will be passed to count.

?

thanks

Re: problem with 'not a single group function' [message #247154 is a reply to message #247153] Sun, 24 June 2007 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are contradiction in your requirements.
Repost it clearly.
What it the result of the query you want?
Don't say how you want to achieve it, just say what you want to achieve.

Regards
Michel
Re: problem with 'not a single group function' [message #247155 is a reply to message #247154] Sun, 24 June 2007 13:53 Go to previous messageGo to next message
kandhola
Messages: 6
Registered: June 2007
Junior Member

see attached file
thanks
Re: problem with 'not a single group function' [message #247156 is a reply to message #247155] Sun, 24 June 2007 14:07 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Don't post doc, xls, and all other files that may contain virus, many of us can't download them.

2/ Say what you want with words. You should have requirements not just a result table.

Regards
Michel
Previous Topic: how to delete all record in the table?
Next Topic: create a user function and calling that function in views
Goto Forum:
  


Current Time: Fri Dec 09 13:30:21 CST 2016

Total time taken to generate the page: 0.10533 seconds