CODEWITHSUNDEEP
sqlsql-servert-sql
Salar Muhammad
Salar Muhammad

Reputation: 334

Count id from other table using sub query in sql

I want to count id's of student according to the specified batches. The student id resides in other table. What I'm trying is to fetch details of all batches from dbo.Batches that contains batchid. At the same time i want to count the number of students enrolled in that batch. The studentid and the batch id is in dbo.studentbatchrelation table. What i've tried so far is, applied sub query to generate deatils of the batches as well as the number of students enrolled in the particular batches.

Below is my query:

Query

(Select 
     Count(studentId) as TotalStudents 
 from 
     dbo.StudentBatchRelation 
 where 
     BatchId in (Select 
                     b.Id as BatchId, b.CourseId as CourseId, 
                     b.BatchNumber as BatchNumber,
                     b.BatchTimingsFrom as BatchTimingsFrom, 
                     b.BatchTimingsTo as BatchTimingsTo, 
                     b.BatchDuration as Duration, b.BatchDate as BatchDate,
                     b."Days" as "Days", b.CourseRoomId as CourseRoomId,  
                     c.CourseName as CourseName, 
                     cr.RoomName as RoomName, 
                     cr.RoomCapacity as RoomCapacity 
                 from 
                     dbo."Batches" b, dbo.Courses c,
                     dbo.CourseRooms cr 
                 where 
                     b.CourseId = c.Id 
                     and b.CourseRoomId = cr.Id 
                     and b.ActiveBatch = 1  ));

Table Structure for Batches

CREATE TABLE [dbo].[Batches] (
    [Id]               VARCHAR (250) NOT NULL,
    [CourseId]         VARCHAR (250) NOT NULL,
    [BatchNumber]      VARCHAR (250) NOT NULL,
    [BatchTimingsFrom] TIME (7)      NOT NULL,
    [BatchTimingsTo]   TIME (7)      NOT NULL,
    [BatchDuration]    VARCHAR (50)  NOT NULL,
    [Days]             VARCHAR (250) NOT NULL,
    [CourseRoomId]     VARCHAR (250) NOT NULL,
    [BatchDate]        VARCHAR (250) NULL,
    [ActiveBatch]      BIT           DEFAULT ((0)) NULL,
    CONSTRAINT [PK_Batches] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_BATCHES_COURSE] FOREIGN KEY ([CourseId]) REFERENCES [dbo].[Courses] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_BATCHES_CourseRoom] FOREIGN KEY ([CourseRoomId]) REFERENCES [dbo].[CourseRooms] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE
);

Table Structure for StudentBatchRelation

CREATE TABLE [dbo].[StudentBatchRelation] (
    [Id]              VARCHAR (250) NOT NULL,
    [BatchId]         VARCHAR (250) NOT NULL,
    [StudentId]       VARCHAR (250) NOT NULL,
    [TransactionId]   VARCHAR (250) NULL,
    [RemainingAmount] VARCHAR (250) NULL,
    [Status]          VARCHAR (250) NULL,
    CONSTRAINT [PK_StudentBatchRelation] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_StudentBatchRelation_Batch] FOREIGN KEY ([BatchId]) REFERENCES [dbo].[Batches] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_StudentBatch_TransactionId] FOREIGN KEY ([TransactionId]) REFERENCES [dbo].[TransactionTable] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT [FK_StudentBatchRelation_Student] FOREIGN KEY ([StudentId]) REFERENCES [dbo].[User] ([Id])
);

Upvotes: 0

Views: 278

Answers (2)

Emre Kabaoglu
Emre Kabaoglu

Reputation: 13146

I could be better to use join;

Select 
     SBR.BatchId, Count(SBR.StudentId) as TotalStudents 
 from 
     dbo.StudentBatchRelation SBR 
     inner join Batches B ON SBR.BatchId = B.Id
     inner join Courses C ON C.Id = B.CourseId
     inner join CourseRooms CR ON CR.Id = B.CourseRoomId
     where B.ActiveBatch = 1
     group by SBR.BatchId

Upvotes: 1

Pawan Kumar
Pawan Kumar

Reputation: 2011

Select 
    b.Id as BatchId, b.CourseId as CourseId, 
    b.BatchNumber as BatchNumber,
    b.BatchTimingsFrom as BatchTimingsFrom, 
    b.BatchTimingsTo as BatchTimingsTo, 
    b.BatchDuration as Duration, b.BatchDate as BatchDate,
    b."Days" as "Days", b.CourseRoomId as CourseRoomId,  
    c.CourseName as CourseName, 
    cr.RoomName as RoomName, 
    cr.RoomCapacity as RoomCapacity 
    ,COUNT(*) OVER (PARTITION BY b.Id) countstudentinBatches
from 
dbo."Batches" b 
INNER JOIN dbo.Courses c ON b.CourseId = c.Id  
INNER JOIN dbo.CourseRooms cr ON b.CourseRoomId = cr.Id 
INNER JOIN dbo.StudentBatchRelation sr ON sr.BatchId = b.Id
WHERE b.ActiveBatch = 1

Upvotes: 0

Related Questions