GROUP BY Clause and HAVING Clause in PHP

GROUP BY :

 The GROUP BY clause is used in conjunction with the SELECT Statement and Aggregate functions to group rows together by common column values.

SELECT with GROUP BY Syntax :

SELECT columns FROM table_name WHERE condition GROUP BY column_name(s);

(Here above "condition" is Optional in above query).

In "personal" table, there are 6 columns named id, name, percentage, age, gender, city.

In "personal" table, column name "id" is PRIMARY KEY and column name "city" is FOREIGN KEY.

In "courses" table, there are 2 columns named course_id and course_name.

In "courses" table, column name "course_id" is PRIMARY KEY.

In "city" table, there are 2 columns named cid and cityname.

In "city" table, column name "cid" is PRIMARY KEY.

SELECT with GROUP BY with Two Tables Syntax :

SELECT columns FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name WHERE condition GROUP BY column_name(s);

SELECT city, COUNT(city) FROM personal GROUP BY city;

If we want to count number of city based on cityname then we can use below shown query :

SELECT c.cityname, COUNT(p.city) FROM personal p INNER JOIN city c ON p.city = c.cid GROUP BY city;

If we want to count number of students which belong to individual city with condition then we can use  below shown query :

SELECT c.cityname, COUNT(p.city) FROM personal p INNER JOIN city c ON p.city = c.cid WHERE p.age >= 20 GROUP BY city;

NOTE : Condition with WHERE clause must be before GROUP BY as shown in above query(POSITION must be maintained).

If we want to arrange records in Ascending Order then we can use below shown query :

SELECT c.cityname, COUNT(p.city) AS Total FROM personal p INNER JOIN city c ON p.city = c.cid GROUP BY city ORDER BY COUNT(p.city);

If we want to arrange Descending Order then we can use below shown query :

SELECT c.cityname, COUNT(p.city) AS Total FROM personal p INNER JOIN city c ON p.city = c.cid GROUP BY city ORDER BY COUNT(p.city) DESC;

NOTE : ORDER BY must be after GROUP BY as shown in above query(POSITION must be maintained).

SELECT with GROUP BY and HAVING Syntax :

SELECT columns FROM table_name GROUP BY column_name(s) HAVING condition;

NOTE : HAVING must be after GROUP BY as shown in above query(POSITION must be maintained).

If we want to select records where number of students are greater than 2 from any city then we can use below shown sql query :

SELECT c.cityname, COUNT(p.city) AS Total FROM personal p INNER JOIN city c ON p.city = c.cid GROUP BY city HAVING COUNT(p.city) > 2 ORDER BY COUNT(p.city);

If we want to arrange above query records in Descending Order then we can use below shown sql query :

SELECT c.cityname, COUNT(p.city) AS Total FROM personal p INNER JOIN city c ON p.city = c.cid GROUP BY city HAVING COUNT(p.city) > 2 ORDER BY COUNT(p.city) DESC;