Lesson 29

How to Count Rows in SQL and Renaming Result Column with Alias

Learn how to use Structured Query Language (SQL) to count the number of rows in a database table.

In particular, you learn how to use the COUNT function to find out the number of rows found with your query. You also learn how to alias a column name with the AS keyword.

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

Summary

Summary of Transcript on Counting Using SQL

In the transcript, the speaker discusses how to efficiently count the number of items sold, specifically keyboards, using SQL queries. Here are the main points:

Manual Counting vs. Automated Counting:
- Counting items manually is manageable for small numbers (e.g., 2 keyboards).
- However, for larger datasets (e.g., a million orders), manual counting is impractical.
Using SQL Queries:
- The speaker introduces SQL commands to count entries in a database.
- Example query: SELECT COUNT(*) FROM orders counts all rows in the orders table.
- To count specific items (e.g., keyboards), one might use additional filtering in queries.
Counting Customer Entries:
- To count the number of customers, the query would be: SELECT COUNT(*) FROM customers.
- The example shows that there are five customers.
Output Clarity with Aliases:
- When running multiple count queries, the output can be unclear without context.
- Using aliases, one can rename output columns for clarity. For instance:
  - SELECT COUNT(*) AS pbar_sales FROM orders will label the output as "pbar_sales".
  - Similarly, SELECT COUNT(*) AS customer_count FROM customers provides clarity for customer counts.
Important Considerations for Aliases:
- Aliases can be created for any column by using the AS keyword.
- To include spaces in alias names, enclose the alias in quotes (e.g., AS "keyboard sales").
- Omitting quotes can lead to errors as the programming language will misinterpret the alias.

By leveraging SQL commands and the use of aliases, counting items and providing clear output becomes a straightforward process.

Video Transcript

Now I'm going to talk about counting things. How can we find out how many keyboards we sold? One way is to go to the table. Okay, you've got orders. Let's count. Okay, one keyboard, two keyboards. Nice, two, right? What if there were a million orders? Would you do that by now? Obviously not, right? So you want the computer to count for you. So how can we do that? Well, let's say select star from orders. If I just did the way this way, I got to do by eye, right? Another way could be okay. Where the item is keyboard, still by eye, right? But a little better. If there were a million rows, I would have to count the a million rows, right? By eye. So let's make the computer count. We're going to say count, Francis started here. Now, if you do that, I've got two. So that's as easy as adding count there. So when you say count, star, blows parentheses, this is going to count the number of rows. It's not going to give you the rows, but rather how many rows were found with this query. So there were two orders of keyboards. So using count, it can count anything. Maybe I want to count how many customers I've had. Select count, star with the parentheses around, round customers. And that will give me how many rows for customers I have. And you can see it's five. Notice I have two separate queries and I can run them and that will give me two separate outputs. The second one is for the customer count. Now, you might notice there's this count, star here. If I'm reading this, I'm like, okay, obviously I have the query. What if I didn't see the query here? And I only saw the output. I don't know what two is, what five is. So usually people can add an adios to rename this heading. So I can say here, after the count, star adds, let's say pbar sales. And if I do that, instead of seeing count, star, I'm gonna see pbar sales in the table heading. And I can do the same for customers as customer count. That way it's clear to me what the output is if I only look at the output. Okay, that's called an alias. You can alias any column you want. Just make sure to add an as to the right-hand side of a column name or whatever operation, in this case, account. And I'm talking about the select area, okay? If you wanna notice I put the words together, if you want to separate by space, make sure to add a quote surrounding it. If I want to keyboard space sales, you can do that but add the quotes there. You know, programming, usually if you don't have the quotes, in this case, it would treat us to separate things usually. I don't know if this would work. See it error, right? Because it doesn't understand what this is, to separate things. So we need the quotes there.