MySQL GROUP BY & HAVING

 How to use Group By clause In MySql Tables:

Group By Syntax:

With Inner Join Syntax:

Code:


CREATE TABLE personal(

id INT NOT NULL,

name VARCHAR(50) NOT NULL,

percentage INT NOT NULL,

age INT NOT NULL,

gender VARCHAR(1) NOT NULL,

city INT NOT NULL,

courses INT NOT NULL,

PRIMARY KEY (id),

FOREIGN KEY (city) REFERENCES City (cid),

FOREIGN KEY (courses) REFERENCES Courses (course_id)

);

INSERT INTO personal(id,name,percentage,age,gender,city,courses)

VALUES

(1,"Aqib","45","13","M",1,1),

(2,"Khadija","56","21","F",2,2),

(3,"Salman Khan","62","20","M",1,1),

(4,"Aqeela","47","18","F",3,1),

(5,"Saqib","74","22","M",1,3),

(6,"Abraham","64","21","M",2,2),

(7,"Shahid","52","20","M",1,3);
create table "city" and insert records
CREATE TABLE city( cid INT NOT NULL AUTO_INCREMENT, cityname VARCHAR(50) NOT NULL, PRIMARY KEY (cid) );
insert records in "city" tableINSERT INTO city(cityname)

VALUES('Mul'),

('Lhr'),

('Mul'),

('Bhw'),

('Mul');

create table "courses" and insert records

CREATE TABLE courses(
    course_id INT NOT NULL AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (course_id)
);

INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA');

GROUP BY

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

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

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

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

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);

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;

How to work Having command in mySql Tables:

Having Syntax:

HAVING

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) > 3
ORDER BY COUNT(p.city) DESC;