SQL DISTINCT, ORDER BY, COUNT

This lessons explains the keywords distinct, order by and count. If you want to order by count or order by sum, go to the chapter Order By Count.

SQL DISTINCT

The purpose of DISTINCT is to prevent the same information to be displayed more than once.

Example: we want to show the cities in which the company had revenues. If we use the following statement, some cities will appear twice in the results :

select city from revenues

Indeed, there can be one line for a city corresponding to revenues from year 2013, and another with the same city corresponding to revenues from year 2014.

In order to see each city only once, we use the following syntax:

select distinct city from revenues

Note that the distinct keyword can be followed by more than one column. In that case, the syntax is the following:

select distinct first_name, last_name from clients

You write the distinct keyword only once, on the left hand side of the first column.

With such a query:

  • If two clients have the same first name but a different last name, they will both appear in the results
  • If two clients have the same last name but a different first name, they will both appear in the results
  • If two clients have the same first name and last name (it is rare, but it can happen), this combination of first name and last name will appear only once.

SQL COUNT(*)

The purpose of SQL COUNT(*) is to count the number of lines returned by the SQL query.

To count the number of rows in the revenues table, we use the following statement:

select count(*) from revenues

You can put the keyword distinct followed by a column name inside the parenthesis instead of a star. In that case, you will get the number of distinct values present in this column.
With the following query, you can get the number of cities in the table revenues:

select count(distinct city) from revenues

SQL ORDER BY

The purpose of the SQL ORDER BY statement is to order the lines appearing in the results.
Example: in the results, you want the customer last name to appear in alphabetical order, from a to z (the letter “a” appearing on the top of the result, “z” on the bottom). You would use the following syntax:

select first_name, last_name, city, account_type
from clients
order by last_name asc

Note that “asc” is the default way of ordering, so writing “asc” is optional.
If you want the name to appear from z to a (“z” appearing on the top of the result, “a” on the bottom), you would use “desc” instead of “asc”:

select first_name, last_name, city, account_type
from clients
order by last_name desc

You may have clients that have the same last name. In that case, you can order the results by last name, and then for each last name order the results by first name:

select first_name, last_name, city, account_type
from clients
order by last_name,first_name asc

When using order by with number:

  • order by … asc would show the highest numbers on the bottom of the results.
  • order by … desc would show the highest numbers on the top of the results.

 

Order by Count

You can use the result of a count to order your results. Example: you want to show the states where your company have clients, showing the states with the most clients on the top.

The syntax is the following:

SELECT state, COUNT(*)
FROM clients
GROUP BY state
ORDER BY COUNT(*) desc

Notice that a group by was necessary to calculate the count, and that you used the count(*) keyword just after order by. Using the same technique, you could order by sum:

SELECT state, sum(revenue)
FROM sales
GROUP BY state
ORDER BY sum(revenue) desc

In this example, we just ordered the state by the total revenue of sales transactions per state.

To test your understanding of this lesson, take the SQL DISTINCT, ORDER BY, COUNT quiz or practice order by with a real database, practice select count part I, practice select count part II. You can also go to the next lesson about SQL where condition.
 
Share on:

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

Author:
Last udpated: September 10, 2017
Created: September 18, 2014