La instrucció GROUP BY agrupa les files que tenen els mateixos valors en files de resum, com ara "trobar el nombre de clients a cada país".
La instrucció GROUP BY s'utilitza sovint amb funcions agregades (COUNT(), MAX(), MIN(), SUM(), AVG()) per agrupar el conjunt de resultats per una o més columnes.
SQL GROUP BY Exemple 1
La següent instrucció SQL enumera el nombre de clients a cada país:
SQL:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
GenQL:
SelectGroupBy<String> countryGroupBy = new SelectGroupBy<String>(CustomersFields.COUNTRY);
SelectCount customerCount = new SelectCount(CustomersFields.CUSTOMERID);
Select2Columns<Long, String> s2c;
s2c = new Select2Columns<Long, String>(customerCount, countryGroupBy);
List<Select2Values<Long, String>> list = getCustomerManager().executeQuery(s2c);
print(list, customerCount.getSelectString(), CustomersFields.COUNTRY.javaName);
SQL GROUP BY Exemple 2
La següent instrucció SQL enumera el nombre de clients a cada país, ordenats de major a menor:
SQL:
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
GenQL:
SelectGroupBy<String> countryGroupBy = new SelectGroupBy<String>(CustomersFields.COUNTRY);
SelectCount customerCount = new SelectCount(CustomersFields.CUSTOMERID);
Select2Columns<Long, String> s2c;
s2c = new Select2Columns<Long, String>(customerCount, countryGroupBy);
OrderBy orderby = new OrderBy(customerCount.getSelectString(), OrderType.DESC);
List<Select2Values<Long, String>> list = getCustomerManager().executeQuery(s2c, orderby);
print(list, customerCount.getSelectString(), CustomersFields.COUNTRY.javaName);
GROUP BY Amb JOIN Exemple
La següent instrucció SQL enumera el nombre de comandes enviades per cada expedidor:
SQL:
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID GROUP BY ShipperName;
GenQL:
SelectGroupBy<String> shipperNameGroupBy;
{
StringField shipperName = new OrdersQueryPath().SHIPPERS().SHIPPERNAME();
shipperNameGroupBy = new SelectGroupBy<String>(shipperName);
}
SelectCount orderCount = new SelectCount(OrdersFields.ORDERID);
Select2Columns<String, Long> s2c;
s2c = new Select2Columns<String, Long>(shipperNameGroupBy, orderCount);
List<Select2Values<String, Long>> list = getOrdersManager().executeQuery(s2c);
print(list, ShippersFields.SHIPPERNAME.javaName, orderCount.getSelectString());