in this video we will talk about group by and having clauses and how to use them with the
aggregate functions for example I have a table like this and I want to get the total gender
grouped by the gender and I want a result like this so in order to get something like this we
will need to use group by function let's see in action how to get a result like this for this we
will use the test database and the company table so let's see what we have inside the
company table select everything from company let's run this as you see we have this data and we want
to count how many male and females we have as we learned before we will use COUNT function in order
to count the number of rows for this I will write SELECT COUNT everything from company so let's run
this to see what we have this will count the total number of rows inside the table company
so now if I want to count the rows per gender what we do we add GROUP BY so let's write
GROUP BY group by what we want to group it by gender so let's run this again
as you see we have four seven as you see we don't have any indication
which one is the female and male for this we have to add the column name after the select so
the column name is gender and comma then run the statement again as you see we have female
and male separated and also if you want to change or give a proper name to this column as we learned
before we will use AS and then let's run the statement again as you see we have a proper column
name and the female and male are grouped so it is good practice whenever you do a GROUP BY something
by a column is better to mention the column name in the select statement as well in order to get
more familiar with the GROUP BY function you will do another practice this time you will group by
department this column and then we get the total salary pair that department as you see we have
salary here and the salary for example you want to get the total salary per department not pair
the staff so as you see we have three department as id admin three or four or more so let's see in
action how we do this so let me run the SELECT query again and now you write the SELECT query
you write SELECT and because we want to group by department we will write the
department column name then then we want the sum of salary from company table so
let's run this and see what will happen as you see we have one department selected by the
the the syntax and a total salary which is not correct so for this we have to add the GROUP
BY clause let's run the query again as you see we have a department and the sum for the salary
peer department so this way we were able to use the GROUP BY with aggregate function now we have
another example is grouping by multiple columns with aggregate function as you see we have an
aggregate function and two column so we can group by gender first then group by address then get the
total salary or the sum of the salary pair this address and their gender so let's see how we do
group by more than one column with the aggregate functions we write select because we want two
columns to be grouped we have to write them after select so I’ll write gender and then write address
then I will write the aggregate function in this example we want the summation of the salary so we
write SUM and salary from company table and then GROUP BY what because we have two columns so we
have to write both of them so I’ll write gender there address now let's run our statement and see
what will happen as you see we have three columns gender address and sum of the salary you see the
salary has been grouped by address then the gender this is how to group multiple columns
with one aggregate function let's go deeper and see how to group multiple columns with
multiple aggregation function in this example we have grouped this table by gender and address
column and we have two aggregate functions one of them is the sum and the other is count so
let's see how to do multiple groups with multiple aggregate function back to our database what we do
we write SELECT and then we write the column names that we want to group them so we write gender
and then we want to the result to be grouped by address column as well and then we write
the aggregate function for example we want SUM of salary and then the other aggregate function
is count we want to count how many male and females per address so we can use Asterixis
or use the id because we are sure the ID is the primary key and never will be null so we can use
it instead of the asterisks after that we write from the table name company and then the other
part is writing the group by gender and then the address and let's close the syntax and run the
select statement and see what will happen as you see the result is as expected we have count column
and SUM column and then grouped by address and gender of course you can give meaningful name or
a proper name for each column using the the alias name using AS as a total
and then for the sum as well and let's run the statement again as you see we have a proper
name for each column now if we want to filter the group for example we group it by gender and then
we filtered by the gender as well we just returned back the male also we grouped them by gender then
by address and then by aggregate function this is also so easy I’ll show you how to do the filtering
in two ways using where clause and having clause so let's go to the database and see how to do it
for saving time we are using the same syntax we use it earlier so now let's filter this result
by gender for example what we can do one of the way is using where clause the WHERE clause
must be used before GROUP BY clause so I will write where gender equals to and let's run the
query as you see the result has been filtered by gender then grouped by gender and then by address
and we have two aggregate function as well and this time let's filter the result by male
as you see only the male gender has been returned back now let's use HAVING clause the HAVING clause
is working same as the where the only difference is we have to write the HAVING clause after GROUP
BY so I will write having gender equal to male and we have to close the syntax
this way we got the same result, you may ask what is the difference between
HAVING and WHERE clause? they are doing the same job and in the performance
perspective they are almost the same one of the difference is when you are using HAVING
the grouping will happen first then the filter will happen but when you are using WHERE clause
the filtering will happen first then the grouping will happen next