How do I create a view with multiple aggregate functions count and sum?

Question

I am trying to create summary for my oracle form. This summary would store number of employees for each department. Records are not stored in one table where would be different column indicating name of department(ex. hr, it ...) code:

create table cleaners
(
ceno INTEGER,
cname VARCHAR(5)
);

create table drivers
(
eno INTEGER,
dname VARCHAR(5)
);

create table mechanics
(
eno INTEGER,
mname VARCHAR(5)
);

INSERT INTO cleaners VALUES ('1','3');
INSERT INTO cleaners VALUES ('2','3');
INSERT INTO cleaners VALUES ('3','3');
INSERT INTO cleaners VALUES ('4','2');
INSERT INTO cleaners VALUES ('5','2');

INSERT INTO drivers VALUES ('5','3');
INSERT INTO drivers VALUES ('4','3');
INSERT INTO drivers VALUES ('3','3');
INSERT INTO drivers VALUES ('2','2');
INSERT INTO drivers VALUES ('1','2');

INSERT INTO mechanics VALUES ('5','3');
INSERT INTO mechanics VALUES ('4','3');
INSERT INTO mechanics VALUES ('3','3');
INSERT INTO mechanics VALUES ('2','2');
INSERT INTO mechanics VALUES ('1','2');


create view summary as select
count(cleaners.eno) as numberofcleaners,
count(drivers.eno) as numberofdrivers,
count(mechanics.eno) as numberofmechanics,
from  cleaners, drivers, mechanics;

So my aim is to have one row with all numbers of each department. However the query is returning multiplied result. How should it be done? I am using Oracle6i, this is a school project nothing commercial.

Answer 1

Since this is a homework assignment, I would like to provide some input on how to design the tables in a proper manner. Just my two cents.

Design suggestion

I would suggest that you alter the table design. All your tables essentially contain the same data:

• a number
• name

Instead of having multiple tables, you should design your database something along this line that would include only two tables.

Department
----------
dept_no   INTEGER
dept_name VARCHAR(30)

Person
------ 
person_no   INTEGER
person_name VARCHAR(30)
dept_no     INTEGER

Create tables with constraints

You should design tables with appropriate constraints. Here are few things to note.

• The constraint PRIMARY KEY, as the name states, would create a primary key on the table to keep the data unique so that you don't end up with multiple ids with the same value.

• The constraint FOREIGN KEY is creating a relation between the tables Department and Person.

• For this example, these keys may not be necessary but in real world applications it is always a best practice to define proper relations using the foreign key constraints. There are lots of other things you can search about PRIMARY KEY and FOREIGN KEY on the web.

• You cannot switch the order in which these tables are created. In other words, you cannot create Person table first and then the Department table. The reason is that Person table is creating a constraint that references the Department table. If you create the Person table first, you will get the error Failed: ORA-00942: table or view does not exist.

• You can also make the dept_no and person_no to be auto incremented numbers so that you don't have to manually insert those numbers. I use SQL Server. So, I am not much familiar with Oracle syntax to make a column to auto incremented number. Search for sequence number for Oracle, you might find something.

Script:

CREATE TABLE Department
(
    dept_no   INTEGER 
  , dept_name VARCHAR(30)
  , CONSTRAINT pk_department PRIMARY KEY (dept_no)
);

CREATE TABLE Person
(
    person_no   INTEGER
  , person_name VARCHAR(30)
  , dept_no     INTEGER
  , CONSTRAINT  pk_person PRIMARY KEY (person_no)
  , CONSTRAINT  fk_person_dept FOREIGN KEY (dept_no)
      REFERENCES Department (dept_no)
);

Populate the table

• Below given script first populates the Department table and then populates the Person table.

• It is actually inserting multiple values into the table at the same time instead of calling INSERT INTO for every single row.

• dual is a dummy table in Oracle with single row. Read here in this SO answer about dual.

Script:

INSERT ALL
  INTO Department (dept_no, dept_name) VALUES (1, 'Cleaner')
  INTO Department (dept_no, dept_name) VALUES (2, 'Driver')
  INTO Department (dept_no, dept_name) VALUES (3, 'Mechanic')
SELECT * FROM dual;

INSERT ALL
  INTO Person (person_no, person_name, dept_no) VALUES (1,  'Cleaner 1',   1)
  INTO Person (person_no, person_name, dept_no) VALUES (2,  'Cleaner 2',   1)
  INTO Person (person_no, person_name, dept_no) VALUES (3,  'Cleaner 3',   1)
  INTO Person (person_no, person_name, dept_no) VALUES (4,  'Cleaner 4',   1)
  INTO Person (person_no, person_name, dept_no) VALUES (5,  'Driver 1',    2)
  INTO Person (person_no, person_name, dept_no) VALUES (6,  'Driver 2',    2)
  INTO Person (person_no, person_name, dept_no) VALUES (7,  'Driver 3',    2)
  INTO Person (person_no, person_name, dept_no) VALUES (8,  'Mechanic 1',  3)
  INTO Person (person_no, person_name, dept_no) VALUES (9,  'Mechanic 2',  3)
  INTO Person (person_no, person_name, dept_no) VALUES (10, 'Mechanic 3',  3)
  INTO Person (person_no, person_name, dept_no) VALUES (11, 'Mechanic 4',  3)
  INTO Person (person_no, person_name, dept_no) VALUES (12, 'Mechanic 5',  3)
  INTO Person (person_no, person_name, dept_no) VALUES (13, 'Mechanic 6',  3)
SELECT * FROM dual;

How to get my data grouped by department?

• Now that you have the table and data, it is time to query the information.

• Your requirement is to get the list of all the departments and the number of people in each department.

• If you run the following query, you will simply get the list of departments but that is not what you need.

Simple Select:

SELECT  dept_no
    ,   dept_name 
FROM    Department;

Output:

DEPT_NO  DEPT_NAME
-------  ---------
   1     Cleaner
   2     Driver
   3     Mechanic

• So, you might ask 'How can I get that information?'. Well, that information is in the table Person. So, we need to join both the tables to find the desired data. We will use INNER JOIN to join both the tables on a common field. In this case, the common field in both the tables is dept_no.

Query that will give you the desired output

Script:

SELECT      d.dept_no
        ,   d.dept_name 
        ,   COUNT(p.person_no) AS No_of_employees
FROM        Department d
INNER JOIN  Person p
ON          p.dept_no = d.dept_no
GROUP BY    d.dept_no
        ,   d.dept_name
ORDER BY    d.dept_no;

Output:

DEPT_NO  DEPT_NAME  NO_OF_EMPLOYEES
-------  ---------  ---------------
   1     Cleaner           4
   2     Driver            3
   3     Mechanic          6

Explanation

• The query uses quite a few things like INNER JOIN, GROUP BY, COUNT and ORDER BY. Let's look each one of these.

• INNER JOIN joins the tables based on the common field, in this case dept_no.

• COUNT function would allow the query group all the count of employees by their department number and department name.

• COUNT is an aggregate function. When you use an aggregate function with non-aggregate columns, then you need to use GROUP BY clause. Here the dept_no and dept_name are non-aggregate columns. SUM, MAX. MIN are some of the aggregate functions.

• Finally, we apply the ORDER BY clause to sort the output by dept_no column.

Demo

Click here to view the demo in SQL Fiddle.

Answer 2

You are doing a cross join in your query. Try with this:

create view summary 
    as 
    select 
    (select count(cleaners.eno) from cleaners) as numberofcleaners, 
    (select count(drivers.eno) from drivers ) as numberofdrivers, 
    (select count(mechanics.eno) from mechanics) as numberofmechanics
from dual;