I am learning Oracle 11g. I do not have any prior knowledge of it.

by Joel
(Ipaja, Lagos, Nigeria)

Select salary, department-id, last-name

From employees
Count(distinct department-id);

What is wrong with the query above? I need help please.

Comments for I am learning Oracle 11g. I do not have any prior knowledge of it.

Average Rating starstarstarstarstar

Click here to add your own comments

Jul 11, 2017
Rating
starstarstarstarstar
Start with the basics
by: John

The error in your code is that employees Count(distinct department-id) is not a valid name for an object (such as a table or a view) in an Oracle database.

count
and distinct need to be in the select cluase in your code when selecting the columns to be used.

EG. SELECT COUNT (DISTINCT column-name)
FROM table-name


When writing SQL it is best to describe the problem in a natural language (such as English, French, German etc) then translate it into SQL.

When writing a query, you need to know what it is you want, then you determine where to get it from.

For your query, you presumably want each employee's salary, the id of the department he/she works for amd his/her name. This is a simple query as all of this information is moset likely in the employees table and can be written like this:
Select salary, department-id, last-name
From employees
.

It is very unlikely that you would want to include the count of disinct department_id in this query as you would have to get this count by employee and in most organisations (and hence data models) an employee only works for one department so the answer would always be one.

If you want to obtain the number of different departments in your Oracle database then, assuming you have one, you would be better of querying the departments table as in most data models this table will have only one row per department. The query in that case would be something like this: SELECT COUNT(department_id) FROM departments;

You don't need to count DISTINCT department_ids because this column will be unique if there is only one row per department.

If you're new to Oracle then start with our SQL tutorials http://www.asktheoracle.net/oracle-sql-tutorial.html.

For more in-depth help, see our Oracle training page for information about on-line and classroom-based formal training.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.