CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Akshay Sawant
Akshay Sawant

Reputation: 53

SQL query to find count of multiple occurrences of column values from a table?

I am facing issue with my SQL query:

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.

Table: "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:

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!

Upvotes: 3

Views: 3191

Answers (2)

Shiju Shaji
Shiju Shaji

Reputation: 1730

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

Upvotes: 4

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 1

Related Questions