0

In the SQL Select function , i am using count & avg function , hence i need to use group by function , however i am not yet understand why i need to use group by function , since i am new in sql. so can someone please explain to me the usage of groupby function

Query:

SELECT usertype, 
concat(start_station_name," to ",end_station_name) as route, 
count(*) as num_trips, 
round(avg(cast(tripduration AS int64)/60),2) as duration 
FROM `bigquery-public-data.new_york_citibike.citibike_trips` 
GROUP BY start_station_name,end_station_name,usertype 
Order by num_trips DESC 
LIMIT 20
Muhammad Saqlain
  • 2,112
  • 4
  • 33
  • 48
kazi jubair Radin
  • 1

1 Answers1

0

The GROUP BY statement groups rows with the same values into summary rows, like "find the number of customers in each country", so here you will count total customers grouped by country. The result will give you two columns, 1 having country names and the other having a corresponding count of customers in that country. So here the grouping is based on a country column.

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

This article will help you better understand the theory: https://www.w3schools.com/sql/sql_groupby.asp

khalidmehmoodawan
  • 598
  • 1
  • 5
  • 22