SQL refresher: Counting and grouping data

0
99


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

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.



Source link

Comments

comments