Grouping and aggregation of SQL data

// Data grouping and aggregation
Group by is used to group results based on the preceding parameter sets.
Select _ state
Form memberdetails
Group by _ state;

Select _ state
From memberdetails
Where _ state in ('Mega State', 'golden State', 'new state ')
Group by _ state;

// You can group multiple columns.
Select _ state
From memberdetails
Where _ state in ('Mega State', 'golden State', 'new state ')
Group by _ state, city;

// Data aggregation function
Count () // used to count the number of records in the result. non-null values are counted. expressions can be used as parameters and wildcards can be inserted *

Select count (*)
From memberdetails;

Select count (city), count (lastname)
From memberdetails;

// According to SQL rules, composite aggregate functions and non-aggregate columns are not allowed.
Select city, count (memberid)
From memberdetails;
// This is not acceptable, because the city may return multiple rows of data, while count only returns one row.

Select _ state, count (lastname)
From memberdetails
Group by _ state;
// This will work.

// Group by essentially divides the results into multiple groups, each of which is a subset of all records

Sum () // used to accumulate results
// Automatically ignore null values

AVG () // used to calculate the average value of the result

Max () // calculate the maximum value
Min () // calculates the minimum value

// The having clause is used to filter groups in the final result. The having clause is similar to a where clause, and its position closely follows the group by clause.

Select category, count (favcategory. categoryid) as popularity
From favcategory inner join category
On favcategory. categoryid = category. categoryid
Group by category. Category
Having count (favcategory. categoryid)> 3
Order by popularity DESC;