How can I enhance my SQL Statement using the SUM function?

I am trying to SUM my UNUSED column (named UNUSED). The challenging part in this context here is, I have a COUNT function. The results are calculated correctly. But how can I add a SUM function in this statement? Appreciate any kind advice.

Following is this SQL statement:

SELECT COUNT(ADMIN_NO)*Courses.PricePerPax AS USED
FROM Student_Prog_Course 
INNER JOIN COURSES ON COURSES.CID=Student_Prog_Course.Course_ID
GROUP BY COURSES.PricePerPax

Result:

Expected Result: 6845.46

COURSES

CREATE TABLE [dbo].[Courses] (
    [CID]              INT           IDENTITY (1, 1) NOT NULL,
    [PFID]             INT           NOT NULL,
    [Start]            DATE          NULL,
    [EndDate]          DATE          NULL,
    [Title]            VARCHAR (50)  NULL,
    [PricePerPax]      FLOAT (53)    NULL,
    [Claim]            FLOAT (53)    NULL,
    [NoOfPax]          INT           NULL,
    [Status]           VARCHAR (50)  NULL,
    [Date_Last_Action] DATE          NULL,
    [Exam]             NCHAR (1)     NULL,
    [PreApproved]      NCHAR (1)     NULL,
    [Comments]         VARCHAR (50)  NULL,
    [Provider]         VARCHAR (50)  NULL,
    [School]           NCHAR (10)    NULL,
    [Remarks]          VARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([CID] ASC),
    CONSTRAINT [FK_Courses_ToTable] FOREIGN KEY ([PFID]) REFERENCES [dbo].[Programme_Funding] ([PID])
);

INSERT INTO [dbo].[Courses] ([CID], [PFID], [Start], [EndDate], [Title], [PricePerPax], [Claim], [NoOfPax], [Status], [Date_Last_Action], [Exam], [PreApproved], [Comments], [Provider], [School], [Remarks]) VALUES (200, 4, N'2020-05-03', N'2020-10-03', N'Accounting Analytics', 89.23, 89.23, 29, N'Pending', N'2020-07-08', N'N', N'N', N'', N'NCS', N'SIT       ', N'')
INSERT INTO [dbo].[Courses] ([CID], [PFID], [Start], [EndDate], [Title], [PricePerPax], [Claim], [NoOfPax], [Status], [Date_Last_Action], [Exam], [PreApproved], [Comments], [Provider], [School], [Remarks]) VALUES (201, 7, N'2020-05-04', N'2020-10-04', N'Understanding ASP.NET c#', 500, 250, 20, N'Pending', N'2020-07-08', N'Y', N'Y', N'NIL', N'NYP', N'SIT       ', NULL)

STUDENT_PROG_COURSE

CREATE TABLE [dbo].[Student_Prog_Course] (
[Admin_No]       VARCHAR (7)  NOT NULL,
[Course_ID]      INT          NOT NULL,
[Course_Status]  TEXT         NOT NULL,
[Claim_ID]       INT          NULL,
[Personal_Email] VARCHAR (50) NULL

);

INSERT INTO [dbo].[Student_Prog_Course] ([Admin_No], [Course_ID], [Course_Status], [Claim_ID], [Personal_Email]) VALUES (N'189097X', 112, N'Completed ', 1, N'<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="33727272737171711d707c7e">[email protected]</a>')
INSERT INTO [dbo].[Student_Prog_Course] ([Admin_No], [Course_ID], [Course_Status], [Claim_ID], [Personal_Email]) VALUES (N'194567C', 112, N'Approved  ', NULL, NULL)
INSERT INTO [dbo].[Student_Prog_Course] ([Admin_No], [Course_ID], [Course_Status], [Claim_ID], [Personal_Email]) VALUES (N'190234A', 112, N'Cancelled ', NULL, NULL)

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Assuming it is USED that is required to be summed, what about:

SELECT  SUM(USED)
FROM    
(
    SELECT COUNT(ADMIN_NO)*Courses.PricePerPax AS USED
    FROM Student_Prog_Course
    INNER JOIN COURSES ON COURSES.CID=Student_Prog_Course.Course_ID
    GROUP BY COURSES.PricePerPax
) u

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating