Group By in mysql

Group By

Get all jobs of the employees:

USE sample;
SELECT job
FROM works_on
GROUP BY job;

Aggregate Functions

The argument of an aggregate function can be preceded by one of two keywords:

-- ALL Indicates that all values of a column are to be considered (ALL is default value)
--DISTINCT Eliminates duplicate values of a column before the aggregate function is applied

Get the number and the last name of the employee with the lowest employee number:

USE sample;
SELECT emp_no, emp_lname
FROM employee
WHERE emp_no =
(SELECT MIN(emp_no)
FROM employee);

Get the employee number of the manager who was entered last in the works_on table:

USE sample;
SELECT emp_no
FROM works_on
WHERE enter_date =
(SELECT MAX(enter_date)
FROM works_on
WHERE job = 'Manager');

Calculate the average of all budgets with an amount greater than $100,000:

USE sample;
SELECT AVG(budget) avg_budget
FROM project
WHERE budget > 100000;

Count all different jobs in each project:

USE sample;
SELECT project_no, COUNT(DISTINCT job) job_count
FROM works_on
GROUP BY project_no;

Get the number of each job in all projects:

USE sample;
SELECT job, COUNT(*) job_count
FROM works_on
GROUP BY job;

HAVING Clause

The HAVING clause defines the condition that is then applied to groups of rows.
Hence, this clause has the same meaning to groups of rows that the WHERE clause

Get project numbers for all projects employing fewer than four persons:

USE sample;
SELECT project_no
FROM works_on
GROUP BY project_no
HAVING COUNT(*) < 4;

Group rows of the works_on table by job and eliminate those jobs that do not begin
with the letter M:

USE sample;
SELECT job
FROM works_on
GROUP BY job
HAVING job LIKE 'M%';
\

----------------------------------------------------------------------------------------

SELECT SalesOrderID, SUM(OrderQty) AS TotalOrderQty
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43660, 43670, 43672)
GROUP BY SalesOrderID;

-------------------------------------------------------------------------------------------

SELECT ManagerID AS Manager, COUNT(*) AS Reports
FROM HumanResources.Employee2
WHERE EmployeeID != 5
GROUP BY ManagerID
HAVING COUNT(*) > 3;