Usage of SQL GROUP BY and Aggregate Functions

In database queries, the GROUP BY statement and aggregate functions in SQL are of great importance for obtaining summary information, preparing reports, or performing analyses. Thanks to SQL GROUP BY and aggregate functions, you can group multiple records according to a specific criterion and easily perform operations such as counting, summing, or averaging for each group.

What are SQL GROUP BY and Aggregate Functions?

SQL GROUP BY is used to group table data by one or more specified columns. Aggregate functions perform bulk operations on these groups. The most commonly used aggregate functions are: COUNT, SUM, AVG, MAX, MIN.

Basic Usage and Examples

Below is a basic example regarding SQL GROUP BY and aggregate functions. Let's consider a table named "satislar":

-- Sample structure of satislar table
-- id | urun_ad | miktar | fiyat

SELECT urun_ad, SUM(miktar) AS toplam_satis
FROM satislar
GROUP BY urun_ad;

This query displays the total sales for each product. With the GROUP BY urun_ad statement, records with the same product are grouped, and the total sales for each group are calculated by using SUM(miktar).

Frequently Used Aggregate Functions

• COUNT(): Returns the number of records per group.
• SUM(): Calculates the sum of all values in a column.
• AVG(): Returns the average value of a column.
• MAX(): Finds the largest value in the group.
• MIN(): Finds the smallest value in the group.

-- Average price of each product
SELECT urun_ad, AVG(fiyat) AS ortalama_fiyat
FROM satislar
GROUP BY urun_ad;

-- Total number of products sold
SELECT COUNT(*) AS toplam_satis_sayisi
FROM satislar;

Using GROUP BY with Multiple Columns

SQL GROUP BY supports multiple columns. Thus, data can be grouped by multiple criteria. For example, product name and sales year can be grouped together:

SELECT urun_ad, YEAR(satis_tarihi) AS yil, SUM(miktar) AS toplam_satis
FROM satislar
GROUP BY urun_ad, YEAR(satis_tarihi);

Conclusion: Effective Analysis with SQL GROUP BY and Aggregate Functions

SQL GROUP BY and aggregate functions are the most effective way to perform powerful and rapid analyses on databases. Especially in large data sets, it is necessary to use these tools effectively to obtain meaningful reports and summaries. As in the code examples, you can write advanced SQL queries by using COUNT, SUM, AVG, MAX, and MIN functions together with GROUP BY. In this way, it is possible to prepare both performant and understandable reports.