Book Image

Data Analysis Using SQL and Excel - Second Edition

By : Gordon S. S. Linoff
Book Image

Data Analysis Using SQL and Excel - Second Edition

By: Gordon S. S. Linoff

Overview of this book

Data Analysis Using SQL and Excel, 2nd Edition shows you how to leverage the two most popular tools for data query and analysis—SQL and Excel—to perform sophisticated data analysis without the need for complex and expensive data mining tools. Written by a leading expert on business data mining, this book shows you how to extract useful business information from relational databases. You'll learn the fundamental techniques before moving into the "where" and "why" of each analysis, and then learn how to design and perform these analyses using SQL and Excel. Examples include SQL and Excel code, and the appendix shows how non-standard constructs are implemented in other major databases, including Oracle and IBM DB2/UDB. The companion website includes datasets and Excel spreadsheets, and the book provides hints, warnings, and technical asides to help you every step of the way. Data Analysis Using SQL and Excel, 2nd Edition shows you how to perform a wide range of sophisticated analyses using these simple tools, sparing you the significant expense of proprietary data mining tools like SAS.
Table of Contents (18 chapters)
Free Chapter
1
Foreword
17
EULA

Exploring Values in Two Columns

Comparing values in more than one column is an important part of data exploration and data analysis. This section focuses on description. Do two states differ by sales? Do customers who purchase more often have larger average purchases? Whether the comparison is statistically significant is covered in the next chapter.

What Are Average Sales by State?

The following two questions are good examples of comparing a numeric value within a categorical value:

  • What is the average order total price by state?
  • What is the average zip code population in a state?

SQL is particularly adept at answering such questions using aggregations.

The following query provides the average sales by state:

SELECT State, AVG(TotalPrice) as avgtotalprice
FROM Orders
GROUP BY State
ORDER BY avgtotalprice DESC

This example uses the aggregation function AVG() to calculate the average.

The following expression could also have been used:

SELECT state, SUM(TotalPrice)/COUNT(*) as avgtotalprice...