CODEWITHSUNDEEP
sqloracle-database
Nate Ho
Nate Ho

Reputation: 51

How do i create a view table to show current number of record of the same employee and the total count? SQL ORACLE

How do i create a view to show the number of total records by name?

CREATE VIEW ViewTest AS
SELECT First_Name || ' ' || last_Name As EMP_NAME,
       to_char(READ_DATE, 'YYYY-MON') As MONTH
  FROM EMPLOYEE E
 INNER JOIN EMPLOYEE_READ ER
    ON E.EMP_ID = ER.EMP_ID
 WHERE to_char(READ_DATE, 'YYYY-MON') = to_char(sysdate, 'YYYY-MON');

This outputs: 

+-------------+----------+
|  EMP_NAME   |  MONTH   |
+-------------+----------+
| Morty_Smith | 2020-NOV |
| Morty_Smith | 2020-NOV |
| Morty_Smith | 2020-NOV |
+-------------+----------+

I am looking to get an output like this..

+-------------+----------+-------+
|  EMP_NAME   |  MONTH   | COUNT |
+-------------+----------+-------+
| MORTY SMITH | 2020-NOV |     3 |
+-------------+----------+-------+

And assuming I have another person with a different name, the view will show this..

+-------------+----------+---------+
|  EMP_NAME   |  MONTH   |  COUNT  |
+-------------+----------+---------+
| MORTY SMITH | 2020-NOV |    3    |
| JERRY SMITH | 2020-NOV |    1    |
+-------------+----------+---------+

Upvotes: 2

Views: 45

Answers (3)

Popeye
Popeye

Reputation: 35920

You can use the following query using GROUP BY:

CREATE VIEW ViewTest AS
SELECT EMP_ID AS ID,
       First_Name ||' '||last_Name As EMP_NAME,
       to_char(SYSDATE,'YYYY-MON') As MONTH, -- you can use SYSDATE here. No need to include it in GROUP BY
       Count(1) as "COUNT"
  FROM EMPLOYEE E 
INNER JOIN EMPLOYEE_READ ER
   ON E.EMP_ID = ER.EMP_ID
WHERE to_char(READ_DATE,'YYYY-MON') = to_char(sysdate,'YYYY-MON')
GROUP BY EMP_ID,
         First_Name,
         Last_name

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Use count() aggregation with group by

SELECT First_Name ||' '||last_Name As EMP_NAME, 
       to_char(READ_DATE,'YYYY-MON') As MONTH ,count(*) as cnt
FROM EMPLOYEE E INNER JOIN EMPLOYEE_READ ER ON E.EMP_ID = ER.EMP_ID
WHERE to_char(READ_DATE,'YYYY-MON') = to_char(sysdate,'YYYY-MON')
group by First_Name ||' '||last_Name,to_char(READ_DATE,'YYYY-MON')

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You just need to add GROUP BY clause along with COUNT aggregation

CREATE OR REPLACE VIEW ViewTest AS
SELECT First_Name || ' ' || last_Name As EMP_NAME,
       TO_CHAR(READ_DATE, 'YYYY-MON') As MONTH,
       COUNT(*)
  FROM EMPLOYEE E
 INNER JOIN EMPLOYEE_READ ER
    ON E.EMP_ID = ER.EMP_ID
 WHERE TO_CHAR(READ_DATE, 'YYYY-MON') = TO_CHAR(sysdate, 'YYYY-MON')
 GROUP BY First_Name || ' ' || last_Name, TO_CHAR(READ_DATE, 'YYYY-MON')

Upvotes: 2

Related Questions