SQL group by

--group by
SELECT d.Name , SUM(Salary), d.ManagerID FROM Employees e JOIN Departments d ON e.DepartmentId = d.DepartmentId
GROUP BY d.Name, d.DepartmentId, d.ManagerID

--many to many join
SELECT e.FirstName + ' '+e.LastName AS [FullName], COUNT(p.Name) AS [Projects COUNT] FROM Employees e
    JOIN EmployeesProjects ep ON e.EmployeeID = ep.EmployeeID
    JOIN Projects p ON ep.ProjectID = p.ProjectID
    GROUP BY DepartmentID, e.FirstName, e.LastName

--group by condition
SELECT DepartmentID, COUNT(EmployeeID) AS COUNT,
    AVG(Salary) AverageSalary
    FROM Employees
    GROUP BY DepartmentID
    HAVING COUNT(EmployeeID) BETWEEN 3 AND 5

--coalesce (what to print if null)
SELECT Name AS [Projects Name],
  COALESCE(EndDate, GETDATE()) AS [END DATE]
FROM Projects