Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 43 - Modular_V2.0_SQL_Flipbook
P. 43

    Stu_id        Stu_name        Subject_group          Class
                                              103        Riya            Arts                       XII
                                              104        Rani            Science                    XII
                                              105        Rita            Science                    XII

                     Let us see the following query:

                        SELECT Subject_group, COUNT(*) AS Enrollment FROM STUDENTS GROUP BY Subject_group;
                     Output:
                                                     +---------------+------------+
                                                     | Subject_group | Enrollment |
                                                     +---------------+------------+
                                                     | Arts          |     1      |
                                                     | Commerce      |     2      |
                                                     | Science       |     2      |
                                                     +---------------+------------+
                     The resultant of the query will be displaying student count subject wise. The GROUP BY also comes
                     with HAVING clause. The HAVING clause is used in the criteria for retrieving records with aggregate

                     functions along with GROUP BY. For example,
                                                               Table-STUDENTS

                                      Stu_id        Stu_name       Subject_group          Class          Marks
                                       101        Rahim           Arts                      XII            97
                                       102        Raja            Commerce                  XII            86
                                       103        Riya            Arts                      XII            45
                                       104        Rani            Science                   XII            67

                                       105        Rita            Science                   XII            90
                        SELECT Subject_group, SUM(Marks) AS "TOTAL" FROM STUDENTS GROUP BY Subject_group
                        HAVING COUNT(*) >= 2;
                                                        +---------------+-------+
                                                        | Subject_group | TOTAL |
                                                        +---------------+-------+
                                                        | Arts          |  142  |
                                                        +---------------+-------+
                                                        | Science       |  157  |
                                                        +---------------+-------+
                     Let us consider another table EMPLOYEE:

                                                               Table-EMPLOYEE

                                       Emp_id        Emp_name       Designation         Salary           City
                                         101        Rahim           VP                  200000      Agra
                                         102        Raja            Manager             150000      Delhi
                                         103        Riya            Manager             150000      Agra
                                         104        Rani            Clerk               15000       Ambala
                                         105        Rita            Clerk               20000       Agra

                        SELECT Designation, SUM(Salary) AS Total_salary FROM EMPLOYEE WHERE City = 'Agra'
                        GROUP BY Designation HAVING COUNT(*) < 10;

                                                                                                                         41
                                                                                                        More on MySQL

| 38 39 40 41 42 43 44 45 46 47 48 |