SQL查询查找表中多次出现列值的计数?

I am facing issue with my SQL query:

我的SQL查询面临问题:

Scenario: I need to generate count of multiple occurences of lecture's time slots for which faculty, subject & lecture date is same. Kindly refer to desired output section and SQL query below. Please let me know what changes can be made to the existing query.

场景:我需要生成多个发生在同一教员、主题和授课日期的讲座时间段的计数。请参阅下面所需的输出部分和SQL查询。请让我知道可以对现有查询做什么更改。

Table: "SCHOOL_RECORDS"

表:“SCHOOL_RECORDS”

FACULTY_NAME   SUBJECT   LECTURE_DATE   LECTURE_TIME_SLOT
----------------------------------------------------------
Alex           Biology   10/01/2015     0900 - 1200
Alex           Biology   10/01/2015     0900 - 1200
Alex           Biology   10/01/2015     0900 - 1200
Lisa           Chemistry 10/01/2015     0900 - 1200
Lisa           Chemistry 10/01/2015     1700 - 2200
Lisa           Chemistry 10/01/2015     1700 - 2200
Lisa           Chemistry 11/01/2015     0900 - 1200

Desired Output:

期望的输出:

FACULTY_NAME   SUBJECT   LECTURE_DATE   LECTURE_TIME_SLOT Count_Of_TimeSlots
----------------------------------------------------------------------------
Alex           Biology   10/01/2015     0900 - 1200       3
Lisa           Chemistry 10/01/2015     0900 - 1200       1
Lisa           Chemistry 10/01/2015     1700 - 2200       2
Lisa           Chemistry 11/01/2015     0900 - 1200       1

Current SQL Query:

当前SQL查询:

Select a.FACULTY_NAME, 
       a.SUBJECT, 
       a.LECTURE_DATE, 
       a.LECTURE_TIME_SLOT, 
       b.Count_Of_TimeSlots

from SCHOOL_RECORDS a
inner join(
           select FACULTY_NAME, SUBJECT, LECTURE_DATE, LECTURE_TIME_SLOT,       
           count(LECTURE_TIME_SLOT) as Count_Of_TimeSlots 
           from SCHOOL_RECORDS 
           group by LECTURE_TIME_SLOT
          ) b 
on a.FACULTY_NAME = b.FACULTY_NAME and a.SUBJECT = b.SUBJECT and      
a.LECTURE_DATE = b.LECTURE_DATE and a.LECTURE_TIME_SLOT =   
b.LECTURE_TIME_SLOT;

Thanks in advance!

提前谢谢!

2 个解决方案

#1

Try this..

试试这个. .

 select FACULTY_NAME, SUBJECT, LECTURE_DATE, LECTURE_TIME_SLOT,       
       count(LECTURE_TIME_SLOT) as Count_Of_TimeSlots 
       from SCHOOL_RECORDS 
       group by FACULTY_NAME, SUBJECT, LECTURE_DATE,LECTURE_TIME_SLOT

#2

SELECT FACULTY_NAME, SUBJECT, LECTURE_DATE, LECTURE_TIME_SLOT, 
       COUNT(*) AS Count_Of_TimeSlots
FROM   SCHOOL_RECORDS
GROUP BY FACULTY_NAME, SUBJECT, LECTURE_DATE, LECTURE_TIME_SLOT