Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 70

The result of SQL-Query-CH02-37 is ugly because of the extra spaces in each row. We can
eliminate these extra spaces by using more advanced functions. The syntax and use of such func-
tions vary from one DBMS to another, however, and a discussion of the features of each product
will take us away from the point of this discussion. To learn more, search on string functions in the
documentation for your specific DBMS product. Just to illustrate the possibilities, however, here is
a Microsoft SQL Server 2012 statement using the RTRIM function that strips the tailing blanks off
the right-hand side of Buyer and Department:

/* *** SQL-Query-CH02-38 *** */
SELECT DISTINCT RTRIM(Buyer)+' in '+RTRIM(Department) AS Sponsor

FROM SKU_DATA;

The result of this query is much more visually pleasing:

Grouping in SQL SeLeCT Statements

In SQL queries, rows can be grouped according to common values using the SQL GROUP
BY clause . For example, if you specify GROUP BY Department in a SELECT statement on the
SKU_DATA table, the DBMS will first sort all rows by Department and then combine all of the
rows having the same value into a group for that department. A grouping will be formed for each
unique value of Department. For example, we can use the GROUP BY clause in the SQL query:

/* *** SQL-Query-CH02-39 *** */
SELECT Department, COUNT(*) AS Dept_SKU_Count
FROM SKU_DATA
GROUP BY Department;

We get the result:

To obtain this result, the DBMS first sorts the rows according to Department and then
counts the number of rows having the same value of Department.
Here is another example of an SQL query using GROUP BY:

/* *** SQL-Query-CH02-40 *** */
SELECT SKU, AVG(ExtendedPrice) AS AvgEP
FROM ORDER_ITEM
GROUP BY SKU;

The result for this query is: