Big Data

SQL refresher: Counting and grouping data

You can get an accurate count of specific data by using the COUNT function in SQL. These simple examples will show you how it works.

suriyadesatitistock-507500754.jpg

Image: iStockphoto.com/SuriyaDesatit

Running a simple "SELECT * " in SQL can get you mounds and mounds of data from your database. Luckily, cleaning up the data output is as simple as adding a few parameters, such as a WHERE clause. You can even have your output provide a total count of specific data in your query. Just use the COUNT function as part of your SELECT statement. Let me show you how with a typical day-to-day data request.

SEE: 5 steps to turn your company's data into profit

An example

Say your marketing department is looking to target previous customers for repeat sales. Its idea is to hit the city or state that has purchased the most widgets within the the last month—in this case, March of 2017. Table A shows the sales table in our database. Let's use it to find out what state had the most sales logged in March 2017.

Table A: sales

nameproductdate_of_purchasestate_code
HANK HILLWIDGET102/22/2017TX
AL BUNDYWIDGET103/03/2017IL
RON SWANSONWIDGET203/15/2017IN
DALE GRIBBLEWIDGET903/09/2017TX
BUD BUNDYWIDGET303/22/2017IL
ANDY DWYERWIDGET203/15/2017IN
LESLIE KNOPEWIDGET103/11/2017IN
SHELDON COOPERWIDGET503/05/2017CA
PEGGY PLATTERWIDGET303/29/2017CO
We'll use the sales table in our example query.

This table gives us ample data to work with. We just need to tailor our SQL syntax to meet our needs. Let's build our query. To use the COUNT function, you include it in your SELECT statement with the field you're trying to count in parentheses: COUNT(field name). You'll also have to use the GROUP BY clause near the end of your query to organize the final output.

In our case, we'll count the name field in the sales table to find out how many customers were in the various states that had sales. Here's the syntax to find the count by shipping state in March 2017:

SELECT COUNT(name), state_code

FROM sales

WHERE date_of_purchase BETWEEN '03/01/2017' AND '03/31/2017'

GROUP BY state_code;

The output should return the following data for your report:

(expression)state_code
1TX
2IL
3IN
1CA
1CO


If you look at the table and compare it to the results of the query, you'll notice that the state of Texas only has a count of 1 even though the table has two records for that state. The variance is the date of the sale. Only one sale met the parameter of BETWEEN '03/01/2017' AND '03/31/2017' .

You can use the same strategy to find out the total number of widgets sold per state. Here's your syntax:

SELECT COUNT (name), product, state_code

FROM sales

GROUP BY product, state_code;

This should return the following for your report:

(expression)productstate_code
1WIDGET1TX
1WIDGET1IL
1WIDGET1IN
2WIDGET2IN
1WIDGET9TX
1WIDGET3IL
1WIDGET3CO

A good place to start

Getting the count of a particular data set may be useful for forecasting or marketing in your enterprise. Understanding historical sales just may be fruitful for future sales. Counting and grouping your data is an easy way to begin your analysis. It's true in business today that data is king. Be sure you're able to poll your data with ease for optimal analysis using SQL.

Also read...

Query questions?

Have you run into any obstacles when building your SQL queries? Share your questions and concerns with fellow TechRepublic members.

About Ant Pruitt

Ant Pruitt is an IT Support Professional with a passion for showing the non-geek how great technology can be. He writes for a variety of tech publications and hosts his own podcast. Ant is also an avid photographer and weight lifter.

Editor's Picks

Free Newsletters, In your Inbox