Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

Using Database in Web Development-Part 8

Friday, June 6th, 2014 by Servage

dd

What about Sorting?

What if I want to sort my data? Do I have to write some customized sorting script to sort the data after I get it out?

Thankfully, no. SQL has already done that work for you. Here’s an example of how you would retrieve and sort all of the last names in our “contacts” table that we created way back in Parts 1 and 2:

  SELECT *
  FROM table
  ORDER BY last_name;

How’s that for easy? The ORDER BY clause tells the DBMS to perform a sort on the data using the “last_name” column. Now the data that you get out will be pre-sorted by last name from A to Z.

So, what if I wanted it to be sorted in reverse order from Z to A? That’s easy too:

SELECT *

FROM table

ORDER BY last_name DESC;

In this case the DESC is used to tell the DBMS to sort by last name in descending order. By default a DBMS will sort in ascending order and you don’t really need to specify it. If you are like me, though, and you want to make it abundantly clear to anyone reading your code what is going on you can use ASC in your ORDER BY clause like this:

ORDER BY last_name ASC

What if I want to sort by more than one column? Can it be done?

Yep. To search by more than one column you list the columns you want to be sorted in the order that you want them sorted. If you wanted to sort by last name and then first name, for example, it would look something like this:

ORDER BY last_name, first_name ASC

Sorting in SQL is straight forward and very very very handy. You will probably find yourself using ORDER BY frequently.

What about Grouping?

 

Imagine this scenario, you are running a gift catalog business for corporations and you want to know how many items have been sold to each corporate client you have. To accomplish this task you will need to learn about COUNT and GROUP BY.

Let’s take a look at COUNT first. COUNT keeps a running total of the number of records retrieved. As a general rule, you will usually put any count totals into a temporary column. Here’s an example of how all of that might work:

SELECT COUNT(*) AS sales_total

FROM sales;

So, what you end up with here is the total number of all of the rows on the “sales” table which will be stored in the temporary column of “sales_total”. Remember, “sales_total” is not a permanent part of your table but is rather a temporary column that you can refer to to get the count number from. You can use whatever name you like as your temporary column name. This may not make much sense with this example but it will be more clear when we do the grouping in a minute.

Now, let’s do a little grouping:

SELECT company_name,

COUNT(*) AS sales_by_company_total

FROM sales

GROUP BY company_name;

What this will do is search the “sales” table and count the total number of rows grouped by the company name which is what we want. The GROUP BY tells the DBMS to group everything by company name. The COUNT then keeps a running total in the temporary column “sales_by_company_total” in accordance with the GROUP BY clause. Notice I changed “sales_total” to “sales_by_company_total” so that it would be a bit more descriptive. I also added the “company_name” column in the table to the SELECT statement so that our output will have both the company’s name and total sales. The output would look something like this:

company_name               sales_by_company_total

—————-     ————————-

Amazon.com     478

Sears     222

CompUSA           512

Dollar General Stores     6

So, what if I wanted to sort the grouped output?

Not a problem, just add your ORDER BY clause at the end like this:

SELECT company_name,

COUNT(*) AS sales_by_company_total

FROM sales

GROUP BY company_name

ORDER BY company_name ASC;

Grouping data can be almost as handy as sorting but you probably won’t find as many occasions to use it. Knowing how to use the built in functions of SQL can save you a great deal of time, effort and frustration. So, before you try writing any custom scripting be sure to thoroughly check out all of the functions available with your DBMS. You will probably be surprised how much has already been done for you.

Sources for Further Reading

Using Database in Web Development-Part 8, 5.0 out of 5 based on 1 rating
Categories: Guides & Tutorials

Keywords: ,

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.