Group by Clause and Aggregated function in SQL || Steem Alliance Community.
Hello Everyone,
How are you, i hope you guys will happy and well. By thre grace of God, i am also well. Today i am here with new post of SQL and PL/SQL series in which i am going to discuss about Distinct key word and order by clause.
Group by clause is basically used with aggregate functions. first of all, we will discuss about aggregate function. then we will move on group by clause. there are many types of aggregate functions that are used in our daily routine development in sql.
Aggregated functions:
- Sum()
- Avg()
- min()
- max()
- count()
First of all we i will share th output of a table b using simple select statement.
NO | sector | cust_id | name_1 | Town |
---|---|---|---|---|
1 | 1000 | 4270603 | SIKANDAR | PESHAWAR |
2 | 1000 | 4270939 | TARIQ | ALI PUR |
3 | 1000 | 5715170 | ALI HAMZA | GALA YOUSAF |
4 | 1000 | 7796882 | IMRAN | KHANEWAL |
5 | 1000 | 953045 | UMAR | YAZMAN |
6 | 1000 | 953045 | UMAR | YAZMAN |
If we want to count the total rows of tables then we will use the count() function. below is the query for table data counting.
select count(cust_id) from users
Result of query.
count(cust_id) |
---|
6 |
To calculate the total value of a particular column within a table, the SUM() function will use. Below is the query for aggregating table data using this function.
select sum(cust_id) from users
Result of query.
sum(cust_id) |
---|
23959684 |
To find the minimum values from a particular column within a table then the min() function will use. Below is the query for aggregating table data using this function.
select min(cust_id) from users
Result of query.
min(cust_id) |
---|
953045 |
I hope things will clear now we move on group by function. group by clause is used to get the result of data from table on group based on depending on the values in one or more columns.
select sum(cust_id) , sector from users group by sector
Result of query.
sector of each row is same so it will sum the cust_id on the base of sector. same sector will group by and then sum function will apply on it and then show the result.
sum(cust_id) | sector |
---|---|
23959684 | 1000 |
Now we will apply the group by function on the base of two colums then see the result.
select sum(cust_id) , sector , name_1 from users group by sector , name_1
Result of query:
NO | sector | cust_id | name_1 |
---|---|---|---|
1 | 1000 | 4270603 | SIKANDAR |
2 | 1000 | 4270939 | TARIQ |
3 | 1000 | 5715170 | ALI HAMZA |
4 | 1000 | 7796882 | IMRAN |
5 | 1000 | 1906090 | UMAR |
In the preceding outcome, I utilized the sum() function with the group by clause to aggregate the cust_id values. The group by function was applied based on the sector and name_1 columns. While the sector remains same across all rows, the name_1 values differ. the data 'Umar' in name_1 appears twice with the same sector, so the group by function will add up these two lines.
It would be my pleasure if you will share your opinion and reviews on this topic
Please cast witness vote to @bangla.Witness or set proxy to @rme.
_
Vote @bangla.witness as witness
Set Proxy @rme as proxy
Special Thanks.
Cc: @rme
Cc: @hungry-griffin
Cc: @steemcurator02
Thanks for giving your precious time to my post.
Follow | Upvote | Share | Comments
Follow me on Instagram.
Instagram
🆃🅷🅰️🅽🅺 🆈🅾️🆄
Thank you, friend!
I'm @steem.history, who is steem witness.
Thank you for witnessvoting for me.
please click it!
(Go to https://steemit.com/~witnesses and type fbslo at the bottom of the page)
The weight is reduced because of the lack of Voting Power. If you vote for me as a witness, you can get my little vote.