Question

I need help with the following problem: Using the classicmodels database you installed from Module 1, provide an example query using both a group by clause and a having clause. Show no more than ten rows of your query result. Discuss if the query you wrote can be rewritten without those clauses. I have come up with the following for the first part of the question: mysql> select offices.city, count(employees.employeenumber) as NumberOfEmployees from Employees -> inner join offices on offices.officecode = employees.officecode -> group by city -> having numberofemployees > 2;

which returns

+---------------+-------------------+

| city | NumberOfEmployees |

+---------------+-------------------+

| San Francisco | 6 |

| Paris | 5 |

| Sydney | 4 |

+---------------+-------------------+

3 rows in set (0.00 sec)

mysql>

I need help with the second part of this question:

Discuss if the query you wrote can be rewritten without those clauses.

I need help rewriting the query to get the same results without using the group by or having clause (please use the classicmodels database)

Answer 1

SELECT offices.city,COUNT(employees.employeenumber) OVER (PARTITION BY employees.officecode) NumberOfEmployees
FROM Employees
inner join offices
ON employees.officecode=offices.officecode
ORDER BY NumberOfEmployees DESC;