Gossamer Forum
Home : General : Databases and SQL :

"not a GROUP BY expression"

Quote Reply
"not a GROUP BY expression"
I cannot run the following query

<--------------------------------------------------------------->

SELECT project_consultant.p_id, SUM(total_hours) Total, project_name, client_name, c_last, c_first, total_hours
FROM consultant, client, project, project_consultant
WHERE consultant.c_id = project.mgr_id
AND client.client_id = project.client_id
AND project.p_id = project.p_id
AND project.p_id = project_consultant.p_id
AND consultant.c_id = project_consultant.c_id
GROUP BY project_consultant.p_id
/

<--------------------------------------------------------------->

It gives the following error

<--------------------------------------------------------------->

SELECT project_consultant.p_id, SUM(total_hours) Total, project_name, client_name, c_last, c_first,
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression

<--------------------------------------------------------------->

To create the tables and insert the records which are required for this question, download this SQL script file.

Quote Reply
Re: [gskahlon79] "not a GROUP BY expression" In reply to
My oracle is a little rusty, but don't you want:

SUM(total_hours) AS Total

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [gskahlon79] "not a GROUP BY expression" In reply to
You don't need the AS to establish column aliases in Oracle.
The problem I believe is that you are joining a bunch of tables and you are not specifying the table.column construct correctly in the first part of your SELECT statement. Also, in the SUM function, you need to use the table.column construct as well.

For more information on Oracle errors, I'd recommend the following websites:

http://otn.oracle.com/
http://www.orafaq.com/faq.htm

or even going to Google and typing in the error message you receive, like:

ORA-00979: not a GROUP BY expression

Example results:

http://www.google.com/...p;btnG=Google+Search
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [gskahlon79] "not a GROUP BY expression" In reply to
This Problem can be solved by following

SELECT project_consultant.p_id, SUM(total_hours) Total, project_name, client_name, c_last, c_first, total_hours
FROM consultant, client, project, project_consultant
WHERE consultant.c_id = project.mgr_id
AND client.client_id = project.client_id
AND project.p_id = project.p_id
AND project.p_id = project_consultant.p_id
AND consultant.c_id = project_consultant.c_id
GROUP BY project_consultant.p_id, project_name, client_name, c_last, c_first, total_hours

I Think This Will execute ur querry but the purpose wont be solved if u really need totalling in your reports then use sqlplus commands like compute and break feature

Pl note that when u r using group by with select statements then u should group by all the columns in the select statement on which no function is executed.

:)