SQL refresher: Counting and grouping data
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
| name | product | date_of_purchase | state_code |
| HANK HILL | WIDGET1 | 02/22/2017 | TX |
| AL BUNDY | WIDGET1 | 03/03/2017 | IL |
| RON SWANSON | WIDGET2 | 03/15/2017 | IN |
| DALE GRIBBLE | WIDGET9 | 03/09/2017 | TX |
| BUD BUNDY | WIDGET3 | 03/22/2017 | IL |
| ANDY DWYER | WIDGET2 | 03/15/2017 | IN |
| LESLIE KNOPE | WIDGET1 | 03/11/2017 | IN |
| SHELDON COOPER | WIDGET5 | 03/05/2017 | CA |
| PEGGY PLATTER | WIDGET3 | 03/29/2017 | CO |
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 |
| 1 | TX |
| 2 | IL |
| 3 | IN |
| 1 | CA |
| 1 | CO |
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) | product | state_code |
| 1 | WIDGET1 | TX |
| 1 | WIDGET1 | IL |
| 1 | WIDGET1 | IN |
| 2 | WIDGET2 | IN |
| 1 | WIDGET9 | TX |
| 1 | WIDGET3 | IL |
| 1 | WIDGET3 | CO |
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.