Database Reference
In-Depth Information
Here the rows have been sorted and grouped by SKU and the average ExtendedPrice for each
group of SKU items has been calculated.
We can include more than one column in a GROUP BY expression. For example, the SQL
statement:
/* *** SQL-Query-CH02-41 *** */
SELECT Department, Buyer, COUNT(*) AS Dept_Buyer_SKU_Count
FROM SKU_DATA
GROUP BY Department, Buyer;
groups rows according to the value of Department first, then according to Buyer, and then
counts the number of rows for each combination of Department and Buyer. The result is:
When using the GROUP BY clause, any and all column names in the SELECT clause that
are not used by or associated with an SQL built-in function must appear in the GROUP BY
clause. In SQL-Query-CH02-42 below, the column name SKU is not used in the GROUP BY
clause, and therefore the query produces an error:
x
/* *** SQL-Query-CH02-42 *** */
SELECT SKU, Department, COUNT(*) AS Dept_SKU_Count
FROM SKU_DATA
GROUP BY Department;
The resulting error message is:
This is the specific Microsoft SQL Server 2012 error message, but other DBMS products will give
you an equivalent error message. Statements like this one are invalid because there are many values
of SKU for each Department group. The DBMS has no place to put those multiple values in the re-
sult. If you do not understand the problem, try to process this statement by hand. It cannot be done.
Of course, the SQL WHERE and ORDER BY clauses can also be used with SELECT state-
ments, as shown in the following query:
/* *** SQL-Query-CH02-43 *** */
SELECT Department, COUNT(*) AS Dept_SKU_Count
FROM SKU_DATA
WHERE SKU <> 302000
GROUP BY Department
ORDER BY Dept_SKU_Count;
The result is:
 
Search WWH ::




Custom Search