#GROUP BY
##SLECT dept FROM employees GROUP By dept; --syntaxx
#The GROUP BY clause in SQL groups rows with the same values in specified columns so you can apply aggregate functions like COUNT or SUM to each group.


CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    desig VARCHAR(50) NOT NULL DEFAULT 'Probation',
    dept VARCHAR(50) NOT NULL,
    salary INT NOT NULL DEFAULT 25000
);

-- Inserting 10 tuples into the employees table
INSERT INTO employees (fname, lname, desig, dept, salary) VALUES 
('John', 'Doe', 'Manager', 'HR', 30000),
('Jane', 'Smith', 'Developer', 'IT', 40000),
('Paul', 'Adams', 'Associate', 'Sales', 27000),
('Lucy', 'Brown', 'Cashier', 'Finance', 23000),
('Ravi', 'Kumar', 'Developer', 'IT', 35000),
('Mina', 'Patel', 'Accountant', 'Finance', 29000),
('Sam', 'Wilson', 'HR Specialist', 'HR', 32000),
('Tina', 'Jones', 'Sales Executive', 'Sales', 26000),
('Alex', 'Morris', 'Team Lead', 'IT', 42000),
('Victor', 'Lee', 'Marketing Head', 'Marketing', 45000);
SELECT desig, dept FROM employees GROUP BY desig, dept; 
#GIVE ME THE COUNT OF IN NO. THAT HOW MANY ARE THERE IN ANY ONE OF THE GROUP
SELECT dept,COUNT(emp_id) FROM employees GROUP BY dept; #group by dept
SELECT desig,COUNT(emp_id) FROM employees group by desig;