-1

Consider I have two tables

Courses            Program
---------------------------
course_ID          program_id
course_title       program_name   
program_ID

Now, I want to check no of courses(by course_id) offered by each program (program_id).

eshirvana
  • 23,227
  • 3
  • 22
  • 38
Abdul Rehman
  • 1

2 Answers2

0
select c.program_id ,p.program_name, count(course_id)
from courses c
join Program p on c.Program_id =p.Program_id
group by program_id,program_name
eshirvana
  • 23,227
  • 3
  • 22
  • 38
0

If I understood you correctly, you're searching for a GROUP BY and a corresponding aggregate.

--Creating sample tables and data
SELECT course_ID, course_title, program_ID
INTO #courses
FROM (
    VALUES  (0, 'course_0', 0),
            (1, 'course_1', 0),
            (2, 'course_2', 0),
            (3, 'course_3', 0),
            (4, 'course_4', 1),
            (5, 'course_5', 1),
            (NULL, 'course_6', 1)
) AS C (course_ID, course_title, program_ID)

SELECT program_ID, program_title
INTO #programs
FROM (
    VALUES  (0, 'program_0'),
            (1, 'program_1')
) AS P (program_ID, program_title)

and after that execute the query

SELECT P.program_title, COUNT(C.course_ID) AS courses_amount
FROM #courses C
INNER JOIN #programs P ON C.program_ID = P.program_ID
GROUP BY P.program_ID, P.program_title

So you basically GROUP BY the value to which you to aggregate to and COUNT the 'course_id'. COUNT(C.course_ID) only counts actual values and will ignore NULLs. If you want to count the NULLs as well, just use COUNT(*).

EDIT: Forgot the result... So it'll look like this:

program_title courses_amount
program_0 4
program_1 2
Dmitrij
  • 1
  • 2