/* 027- GROUP BY & HAVING */
-- create table "city" and insert records --
CREATE TABLE city(
cid INT NOT NULL AUTO_INCREMENT,
cityname VARCHAR(50) NOT NULL,
PRIMARY KEY (cid)
);
INSERT INTO city(cityname)
VALUES('Agra'),
('Delhi'),
('Bhopal'),
('Jaipur'),
('Noida');
-- 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');
-- create table "tOne" and insert records
CREATE TABLE tOne(
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 tOne(id,name,percentage,age,gender,city,courses)
VALUES
(1,"Ram Kumar","45","13","M",1,1),
(2,"Sarita Kumari","56","21","F",2,2),
(3,"Salman Khan","62","20","M",1,1),
(4,"Juhi Chawla","47","18","F",3,1),
(5,"Anil Kapoor","74","22","M",1,3),
(6,"John Abraham","64","21","M",2,2),
(7,"Shahid Kapoor","52","20","M",1,3);
-- GROUP BY --
SELECT city,COUNT(city)
FROM tOne
GROUP BY city;
SELECT c.cityname,COUNT(p.city)
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city;
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city;
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne 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 tOne 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 tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city
ORDER BY COUNT(p.city) DESC;
-- HAVING (use for applying conditions on the table came after the group by options)
SELECT c.cityname,COUNT(p.city) AS Total
FROM tOne p INNER JOIN city c
ON p.city = c.cid
GROUP BY city
HAVING COUNT(p.city) > 3
ORDER BY COUNT(p.city) DESC;