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    |
+-------------+----------+---------+

Answer

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')

Advertisement

Answer