0

Hi all !
I 'm a newbie in SQL and has just install the Oracle 10g Express Edition. When using its SQL Command in the Web-interface, I took an error with the GROUP BY, as follow :
First, I create a table DEPARTMENT, here 's the code :

CREATE TABLE DEPARTMENT
(
     Dname          VARCHAR(15)          NOT NULL,
     Dnumber        INT                  NOT NULL,
     Mgr_ssn        CHAR(9)              NOT NULL,
     Mgr_start_date DATE,
     PRIMARY KEY(Dnumber),
     UNIQUE(Dname)
     --FOREIGN KEY(Mgr_ssn) REFERENCES EMPLOYEE(Ssn)
);

Then I insert some values into it, using the "INSERT INTO ... VALUES ... " statement, here 's my result :

DNAME DNUMBER MGR_SSN MGR_START_DATE
Research 5 333445555 22-MAY-88
Administration 4 987654321 01-JAN-95
Headquarters 1 888665555 19-JUN-81

When I try

select Dname from DEPARTMENT GROUP BY Dname;

It goes perfectly and I get :
DNAME
Administration
Headquarters
Research

But when I use

select Dname, Dnumber from DEPARTMENT GROUP BY Dname;

I took a "ORA-00979: not a GROUP BY expression" error ... ?
I try a similar one

select Dname, Dnumber
from DEPARTMENT GROUP BY Dname, Dnumber;

and get good result :
DNAME DNUMBER
Headquarters 1
Administration 4
Research 5

But in this case, I have no sense about how it work ... ?
Please explain me ... :confused: Thank U very much ! ^^

3
Contributors
5
Replies
6
Views
7 Years
Discussion Span
Last Post by Obelisk4
0

Sorry because I don 't know how to edit my result table >"<

0

group by is used to aggregate you data, you dont need group by so remove group by clause from your query. following is your query

SELECT Dname, Dnumber FROM DEPARTMENT
0

By the way ... is there any difference in the syntax of SQL in MySQL and Oracle ? My Professor advised us to install Oracle, but he always uses MySQL ... ? >"<

0

There are a few differences between oracle and mysql's dialect of sql. Usually extras each has added. The basics of sql are very very similar in each db however. That's the whole point of sql.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.