Database Design and SQL

SQL tutorial


Previous page Previous     TOC Contents     Next pageNext


Lesson 6 - Sorting and grouping


Displaying results in order

It may not have been mentionned specifically yet: in a database table, there is no way to input data in a given order. In other words, if you add a row to a table you cannot insert it between other rows so that it comes out automatically in alphabetical order. Whenever a new row is added, it is simply appended to the end of the table.





If you want the rows to come out in a given order you have to sort them. To sort rows you use the ORDER BY clause in the SELECT statement.

The syntax of the ORDER BY clause is:
SELECT select_list
FROM table
ORDER BY expression [ASC DESC];


ASC stands for Ascending order and it is the default value
DESC is used to sort in Descending order


To list all projects in order of their StartDate, with the oldest first (smallest date):

SELECT p_Number, p_Title, p_StartDate, p_EndDate
          FROM Project
          ORDER BY p_StartDate;


To get the list in reverse order, with the most recent at the beginning, use the same SELECT but add the DESC option:

SELECT p_Number, p_Title, p_StartDate, p_EndDate
          FROM Project
          ORDER BY p_StartDate DESC;


You can also have a sort within a sort by specifying 2 sort fields. The most common example of that is sorting in First name order within Last name - all Smiths will be in First name order, etc.

Note that the main sort field is named first, the secondary sort field is second and so on. In this case Last name is the main sort order:

SELECT e_Id, e_Fname, e_Lname
          FROM Employee
          ORDER BY e_Lname, e_Fname;


What if you have a calculated expression that you want to sort on.

If you need to list the length of all projects and sort on that expression:

SELECT p_Number, p_Title, p_StartDate, p_EndDate,
    Datediff('m', p_StartDate, p_EndDate) AS [Project length]
          FROM Project
          ORDER BY 5;


There are 5 elements in the select_list. 'ORDER BY 5' specifies to sort on the fifth element, the calculated field. You could do the same for any other sort specification. For example, 'ORDER BY 2' in this example will sort in order of p_Title.



Top









GROUPING DATA

For this next section we are going to use a new database called BookStor. You can download it now from the Download area. Select the version that you need. For this lesson we'll use the Authors table only. But, keep the database handy. Later, when we get to Lesson 9, we'll use the other tables and Queries in BookStor to look at more advanced concepts such as Crosstab queries and Union queries, etc.

In the previous lesson, we learned how to use the aggregate functions to produce summary information on sets of data.

From my table of authors, I want to know how many authors produce 'Romance' novels. I use a simple query with the aggregate function:
SELECT COUNT(au_id) AS [Number of authors]
          FROM Authors
          WHERE au_subject = 'Romance';


But, what if I want to know how many authors I have in each category, even if I don't know what the categories are? It can't be done with a simple Select with aggregate.

The answer is a new clause called: GROUP BY which is used with the Select.

The syntax of the GROUP BY clause is:

SELECT select-list
FROM table
GROUP BY group_by_list;


To answer the question above:
SELECT au_subject AS [Subject], COUNT(au_id) AS [Number of authors]
          FROM Authors
          GROUP BY au_subject;


Want to know how many authors in each state and what their average salary is?

SELECT au_state AS [State],
COUNT(au_id) AS [Number of authors],
AVG(au_salary) AS [Average salary]
          FROM Authors
          GROUP BY au_state;


For practice, let's apply the stuff from the beginning of this lesson. I want the results in order of Average salary. That's easy:

SELECT au_state AS [State],
COUNT(au_id) AS [Number of authors],
AVG(au_salary) AS [Average salary]
          FROM Authors
          GROUP BY au_state
          ORDER BY 3;


Remember that ORDER BY 3 means the third item in the select_list.


Now, let's say you don't want to see details of all the states in the Authors table, you just want to see authors from Utah and Kansas.

There is another clause to select groups, the same way as the WHERE clause selects rows. That clause is called HAVING and is used the same as the WHERE but, on groups.

SELECT au_state AS [State],
COUNT(au_id) AS [Number of authors],
AVG(au_salary) AS [Average salary]
          FROM Authors
          GROUP BY au_state
          HAVING au_state IN ('UT', 'KS');


Note the use of the IN operator to mean 'If the author's state is in this list, the row is selected'.
The clause could also have been written as: HAVING au_state = 'KS' OR au_state = 'UT'.



You can also group data on more than one column.

How many authors of each category are there in each state?

SELECT au_state, au_subject, COUNT(au_subject) AS [Number]
          FROM authors
          GROUP BY au_state, au_subject
          ORDER BY 2;



If you want to see it displayed differently, just change the sort order:

SELECT au_state, au_subject, COUNT(au_subject) AS [Number]
          FROM authors
          GROUP BY au_state, au_subject
          ORDER BY 1;








If you haven't found the database resource you're looking for,
use our Google Search box for more information.

Google






Top



Home | Tutorials | Contact | Sitemap | Add URL | Privacy policy