Скачать или смотреть How To: Aggregate Functions In MySQL (3 Min) | AVG, SUM, COUNT, MIN, MAX with GROUP BY, ORDER BY

In this tutorial, you'll learn how to use aggregate functions in MySQL using AVG, SUM, COUNT, MIN, MAX and GROUP BY, ORDER BY, HAVING clause.

—
Facebook:   / gokcedbsql  
—
Video Transcript:
—
Connect MySQL In Python:    • How To Connect To MySQL In Python (2 Min) ...  
—
Hi guys, this is Abhi from Gokcedb. In this video, you're going to learn how to use Aggregate Function In MySQL. Let's start by looking at the entity relationship diagram.

I have four tables, but in this tutorial, I'll mostly be running queries on the property and the customer table. Say I wanted to get a count of all the rows in the property table. I'll use the select count star from the table syntax.

As you can see, the property table contains 396 properties belonging to a subdivision. Use the select count property underscore subdivision in parentheses from table syntax. As we can see 281 of the 396 properties belong to a subdivision.

To get a count of all the unique subdivisions, add the distinct keyword in front of the property underscore subdivision column. To add a custom column header name in the output, use the as keyword followed by the name in single quotes. Say you wanted to know the average price you paid for the property.

To do that, use the select average property underscore cost underscore basis from table syntax to round the output to 2 decimal places using the round keyword in front of the average and comma 2 at the end. Let's say we wanted to find out how much total cash we paid to buy all 396 properties. To do that, let's multiply the average property cost basis with the count star.

Alternatively, you can use the select sum property underscores cost underscore basis from table syntax to get the same number. Use the max function on the property cost basis column to find out what max you paid for a property. Similarly, using the min function on the property cost basis column will give you the minimum amount you paid for the property.

To find out what's the maximum amount you paid for a property by state use the group by clause at the end followed by the column name. You can also use the order by clause to sort based on a column. Use the desc keyword at the end for a descending sort to further restrict the output where the max of cost spaces is greater than 5000.

Use the having clause after group by. Let's look at one last example where we want to see a count of customers by state. Will also sort the output by the count column in descending order.

There you have it. Make sure you like, subscribe, and turn on the notification bell until next time.