Home » SQL & PL/SQL » SQL & PL/SQL » Sum With Select problem (merged 4)
Sum With Select problem (merged 4) [message #413156] Tue, 14 July 2009 11:34 Go to next message
maher534510
Messages: 25
Registered: September 2008
Location: jordan
Junior Member
select sum(v.interval),e.emp_id ,to_char( e.hire_date,'dd/mm/yyyy'),v.id , to_char(v.DATE_FROM,'dd/mm/yyyy')
,v.INTERVAL,to_char(sysdate,'yyyy'),to_char(sysdate,'yyyy') - to_char(e.hire_date,'yyyy')

from employees e , vacation v
where e.emp_id=69 and e.emp_id =v.emp_id and to_char(v.DATE_FROM,'yyyy')=to_char(sysdate,'yyyy')
GROUP BY v.emp_id;


ORA-00979: not a GROUP BY expression

what is the wrong??
Re: Sum With Select problem [message #413161 is a reply to message #413156] Tue, 14 July 2009 11:41 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SELECT   Sum(v.INTERVAL),
         e.emp_id,
         To_char(e.hire_date,'dd/mm/yyyy'),
         v.id,
         To_char(v.date_from,'dd/mm/yyyy'),
         v.INTERVAL,
         To_char(SYSDATE,'yyyy'),
         To_char(SYSDATE,'yyyy') - To_char(e.hire_date,'yyyy')
FROM     employees e,
         vacation v
WHERE    e.emp_id = 69
         AND e.emp_id = v.emp_id
         AND To_char(v.date_from,'yyyy') = To_char(SYSDATE,'yyyy')
GROUP BY v.emp_id; 


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

>what is the wrong??

ORA-00979: not a GROUP BY expression

the GROUP BY clause needs more fields
Re: Sum With Select problem (merged 4) [message #413163 is a reply to message #413156] Tue, 14 July 2009 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session.

Do not multipost your question.

Regards
Michel
Re: Sum With Select problem [message #413166 is a reply to message #413161] Tue, 14 July 2009 12:11 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
BlackSwan wrote on Tue, 14 July 2009 12:41

the GROUP BY clause needs more fields



...and you are grouping by something that isn't even in your SELECT list.
Re: Sum With Select problem [message #413168 is a reply to message #413166] Tue, 14 July 2009 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
joy_division wrote on Tue, 14 July 2009 19:11
BlackSwan wrote on Tue, 14 July 2009 12:41

the GROUP BY clause needs more fields



...and you are grouping by something that isn't even in your SELECT list.

But can be done.

Regards
Michel

Re: Sum With Select problem [message #413204 is a reply to message #413168] Tue, 14 July 2009 15:14 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Michel Cadot wrote on Tue, 14 July 2009 13:40
joy_division wrote on Tue, 14 July 2009 19:11
BlackSwan wrote on Tue, 14 July 2009 12:41

the GROUP BY clause needs more fields



...and you are grouping by something that isn't even in your SELECT list.

But can be done.



Based on the messy query, I was just pointing out another possible error in OP's ways, not necessarily an error in using it. It's almost as if the GROUP BY came from a different query and was just plugged in here.
Re: Sum With Select problem (merged 4) [message #413209 is a reply to message #413156] Tue, 14 July 2009 15:31 Go to previous messageGo to next message
maher534510
Messages: 25
Registered: September 2008
Location: jordan
Junior Member
so what is the correct syntax for writing my sentence above
Re: Sum With Select problem (merged 4) [message #413210 is a reply to message #413209] Tue, 14 July 2009 15:41 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Depends on what the data is and what the expected result is.

Nobody will be able to figure out a "correct syntax" just by seeing the wrong syntax and nothing else.

But you definitely need ALL columns in the GROUP BY clause that don't have group functions like sum(), avg(), max(), min(), etc.. applied in the select list.
Previous Topic: Getting the list of columns of a VIEW from data dictionary in pl sql
Next Topic: Pass Variable to cursor
Goto Forum:
  


Current Time: Thu Dec 08 10:06:05 CST 2016

Total time taken to generate the page: 0.17864 seconds