Using Goup By in Procedure - SQLServer-CodePudding

I have two tables in my database:

/* Create class table */
CREATE TABLE Class
(
    CId INT NOT NULL,
    ClassName VARCHAR (50) NOT NULL,
    ClassDescription VARCHAR (MAX) NULL,
    ClassStatus VARCHAR (50) NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    Degree VARCHAR (50) NOT NULL,
    TeacherName VARCHAR (50) NOT NULL,
    ClassTopic VARCHAR (50) NOT NULL,
    CONSTRAINT CHK_Dates CHECK (EndDate > StartDate),
    CONSTRAINT CHK_Status CHECK (ClassStatus = 'Active' Or ClassStatus = 'NAct' Or ClassStatus = 'Archive'),
    CONSTRAINT CHK_Degree CHECK (Degree = '1st' Or
                                    Degree = '2nd' Or
                                    Degree = '3rd' Or
                                    Degree = '4th' Or
                                    Degree = '5th' Or
                                    Degree = '6th' Or
                                    Degree = '7th' Or
                                    Degree = '8th' Or
                                    Degree = '9th' Or
                                    Degree = '10th' Or
                                    Degree = '11th' Or
                                    Degree = '12th'),
    CONSTRAINT CHK_Topic CHECK (ClassTopic = 'Mth' Or
                                    ClassTopic = 'Ph' Or
                                    ClassTopic = 'Ch' Or
                                    ClassTopic = 'Bio' Or
                                    ClassTopic = 'Fr' Or
                                    ClassTopic = 'En' Or
                                    ClassTopic = 'Arb' Or
                                    ClassTopic = 'Rgs'),
    PRIMARY KEY (CId)
);

/* Create Student table*/
CREATE TABLE Student
(
    _SId INT NOT NULL,
    UserName VARCHAR (50) NOT NULL,
    Bdate DATE NOT NULL,
    SPassword VARCHAR (50) NOT NULL,
    SName VARCHAR (50) NOT NULL,
    SLastName VARCHAR (50) NOT NULL,
    NationalCode VARCHAR (10) NOT NULL,
    Email VARCHAR (MAX) NULL,
    StudentClass INT NOT NULL,
    HomePhone VARCHAR (8) NOT NULL,
    CONSTRAINT CHK_Email CHECK (Email like '%_@__%.__%'),
    PRIMARY KEY (_SId),
    FOREIGN KEY (StudentClass) REFERENCES Class (CId) ON DELETE CASCADE
);
ALTER TABLE Student
ADD CONSTRAINT New_CHK_NCode CHECK (NationalCode LIKE '%[0-9]%');

ALTER TABLE Student
ADD CONSTRAINT New_CHK_Phone CHECK (HomePhone LIKE '%[0-9]%');

I have inserted the below records in each of them:

USE School


/* Inserting data into tables */

INSERT INTO dbo.Class (CId, ClassName, ClassDescription, ClassStatus, StartDate, EndDate, Degree, TeacherName,  ClassTopic)
VALUES (1, 'aaa', NULL, 'Active', '20020907', '20030907', '1st','Eetemadi', 'Mth'),
        (2, 'bbb', NULL, 'Active', '20020907', '20030907', '1st','Rahmani', 'Ph'),
        (3, 'ccc', NULL, 'Active', '20020907', '20030907', '2nd','Entezari', 'Ch'),
        (4, 'ddd', NULL, 'Active', '20020907', '20030907', '2nd','Beytollahi', 'Bio'),
        (5, 'eee', NULL, 'Active', '20020907', '20030907', '3rd','Zahirpour', 'Fr');

INSERT INTO dbo.Student (_SId, UserName, Bdate, SPassword, SName, SLastName, NationalCode, Email, StudentClass, HomePhone)
VALUES (1, 'aaa', '20020807', '1234', 'maryam', 'vahdati', '1234567890', '[email protected]', 1, '12345678'),
        (2, 'bbb', '20020707', '4321', 'marjan', 'vahdati', '1234578906', '[email protected]', 1, '12345678'),
        (3, 'ccc', '20020607', '1342', 'masomeh', 'vahdati', '1234567809', '[email protected]', 2, '12345678'),
        (4, 'ddd', '20020507', '1243', 'mohammad', 'vahdati', '1234568907', '[email protected]', 2, '12345678'),
        (5, 'eee', '20020407', '1342', 'mahmod', 'vahdati', '1245678903', '[email protected]', 3, '12345678');

Now I have to write a query in a Procedure to group classes based on degrees, show the number of students in every grade, and also the total number of classes. I have written the below query:

ALTER PROCEDURE dbo.FirstReport
AS
BEGIN
    Select Degree, COUNT(Degree) as numberOfClasses, COUNT(StudentClass) as numberOfStudents
    FROM Class C left outer join Student S ON C.CId = S.StudentClass
    GROUP BY Degree

    UNION ALL
    SELECT 'SUM' Degree, COUNT(Degree), COUNT(StudentClass)
    FROM Class C join Student S ON C.CId = S.StudentClass;
END

The output is:

| Degree| numberOfClasses | numberOfStudents|
|:-----------------------------------------:|
|1st    |           4     |        4        |
|2nd    |           2     |        1        |
|3rd    |           1     |        0        |
|SUM    |           5     |        5        |

But the numberOfClasses must be 2 when the Degree is 1st.

I do not know how to make it correct. I will be grateful for your help.

CodePudding user response：

As I mentioned in the comments, you have a many to one join here, and thus the COUNT you get is correct, due said one to many join; you have 2 rows that each join to 2 other rows and 2 * (1 * 2) = 4.

Instead, use a DISTINCT on your first COUNT on the ID column. Also, there's no need for a UNION ALL; you can use GROUPING SETS or ROLLUP to get the "grand total" row:

SELECT ISNULL(C.Degree,'SUM') AS Degree,
       COUNT(DISTINCT C.CId) AS NumberOfClasses,
       COUNT(S.StudentClass) AS NumberOfStudents
FROM dbo.Class C
     LEFT OUTER JOIN dbo.Student S ON C.CId = S.StudentClass
GROUP BY GROUPING SETS(Degree,());