CODEWITHSUNDEEP
sqloracle-databaseviewcount
The_Bear
The_Bear

Reputation: 173

Create View with Count in SQL

I need to create a View in SQL by counting the number of tasks an employee has been given but only show an employee who has more than 3 tasks. The problem I got is showing the count in the view. The error I'm getting is "missing right parenthesis"

CREATE VIEW EMPLOYEE_TASKS (E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, 
T.TASK_ID)

AS SELECT COUNT T.TASK_ID

FROM EMPLOYEE E, TASK T

WHERE E.EMPLOYEE_ID = T.TASK_ID

AND COUNT(T.TASK_ID) > 3;

Upvotes: 0

Views: 5432

Answers (3)

Sentinel
Sentinel

Reputation: 6449

When creating a view you can optionally provide a parenthetical list of column names just after the view name, however, that list should not be in alias.column_name format, you just provided the column names. Furthermore, if you do provide the optional list of column names, the projection (select list) of the query which defines the view should have the same number of column in its projection as in the column alias list.

Currently you have the following problems with your view definition:

  1. You have table aliases (E. and T.) in the column alias list
  2. You have only one column in the query projection but four aliases in the views column alias list
  3. COUNT T.TASK_ID is not a valid column expression, count(t.task_id) is.
  4. You use COUNT(T.TASK_ID) in the WHERE clause, however, that's an aggregate function which isn't allowed in the WHEREclause.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270431

Your code just makes no sense. I suspect you want:

CREATE VIEW EMPLOYEE_TASKS AS
    SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, COUNT(*) as num_tasks
    FROM EMPLOYEE E JOIN
         TASK T
         ON E.EMPLOYEE_ID = T.TASK_ID
    GROUP BY E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME
    HAVING COUNT(*) > 3;

Upvotes: 2

Setop
Setop

Reputation: 2500

"count" is a function. it needs arguments in parenthesis :

...

AS SELECT COUNT(T.TASK_ID)

...

Upvotes: 0

Related Questions