5. Counting the Number of Rows and Columns

Counting the Number of Rows and Columns

Task #1: Counting Rows

To begin our exploration of the dataset, a fundamental step is to understand its size. Knowing the number of rows helps in assessing the dataset's scale and informs subsequent data analysis processes.

Objective: The aim is to count the total number of rows in the dataset, which represents the number of records or entries.

Expected Result: The execution of this query should reveal that the dataset contains 9,994 rows.

Data Source: dataacademykz.superstore.sales

Click To Show / Hide Solution

 

SELECT
  COUNT(*) AS rows_number
FROM
  `dataacademykz.superstore.sales`

Explanation:

Imagine you’re looking at a long list of every sale your store has ever made, and you want to know just how big that list is.

• This query is like flipping to the last page and seeing a number that tells you exactly how many sales records are in there.
• It counts every single entry in your sales records, giving you the total number.

Knowing how many sales transactions are in your records helps you understand the size of your dataset, which is a good starting point for any further analysis you might want to do.

Technical Breakdown:

This SQL query is designed to determine the total number of rows in the Superstore sales dataset, giving an insight into the dataset's scale.

• Use of COUNT(*):

  • The query uses COUNT(*) to count all rows in the dataset.
  • The asterisk (*) in COUNT(*) signifies that every row is counted, regardless of whether any column values are null or not.

• Data Source:

  • The data is retrieved from the dataacademykz.superstore.sales table, which presumably contains sales transaction records.

• Result:

  • The output will be a single numerical value under the alias rows_number, representing the total count of rows in the Superstore sales dataset.

Task #2: Counting Columns

After establishing the number of rows in our dataset, the next step in our initial exploration is to determine the number of columns. This information is crucial as it gives us an insight into the dataset's complexity and the variety of data points available for analysis.

Objective: Ascertain the total number of columns in a specific table of our dataset.

Expected Result: The query is expected to show that there are 21 columns in the 'sales' table of the 'superstore' dataset.

Data Source: dataacademykz.superstore.sales

Click To Show / Hide Solution 

SELECT
  COUNT(*) AS total_columns
FROM
  `dataacademykz.superstore.INFORMATION_SCHEMA.COLUMNS`
WHERE
  table_name = 'sales'
  

Explanation:

Consider the electronic table containing all sales information of your store and try to estimate how much information you have about each sale (for example, date, customer name, amount, etc.).

• This query counts the number of different types of information categories or fields in your sales electronic table.
• It gives the total count of these categories or fields.

This helps to understand the breadth of data associated with each sale, which is crucial for knowing how extensive your sales records are.

Technical breakdown:

This SQL query aims to identify the total count of fields in the "sales" table of the "superstore" database, which facilitates understanding the consistency of the data set.

• Objective: To count the total number of fields in a specific table, in this case, the fields in the "sales" table of the "superstore" database.

• Using INFORMATION_SCHEMA.COLUMNS:

• Selects metadata from the INFORMATION_SCHEMA.COLUMNS table, providing information about all columns in all tables in the database.
• Counts all records corresponding to the columns in the indicated table within the metadata table.

• Filtering with WHERE clause:

• WHERE clause restricts counting to only the specified table and schema in the database.
• table_name = 'sales' filters the count to only include the columns within the "sales" table of the "superstore" database.

• Result:

• The result is a single numerical value named total_columns indicating the total count of fields in the specified table..