Verileri gruplama

Orta Düzey SQL

Jasmin Ludolf

Data Science Content Developer, DataCamp

Verileri gruplama

Farklı şekillerin üç kategoriye (kare, daire, üçgen) ayrıldığı bir huni görseli

Tek alanla GROUP BY

SELECT certification, COUNT(title) AS title_count
FROM films
GROUP BY certification;

|certification|title_count|
|-------------|-----------|
|Unrated      |62         |
|M            |5          |
|G            |112        |
|NC-17        |7          |
...

Hata yönetimi

SELECT certification, title
FROM films
GROUP BY certification;

column "films.title" must appear in the
GROUP BY clause or be used in an 
aggregate function
LINE 1: SELECT certification, title
                              ^

SELECT 
   certification, 
   COUNT(title) AS count_title
FROM films
GROUP BY certification;

|certification|count_title|
|-------------|-----------|
|Unrated      |62         |
|M            |5          |
|G            |112        |
...

Birden çok alanla GROUP BY

SELECT certification, language, COUNT(title) AS title_count
FROM films
GROUP BY certification, language;

|certification|language |title_count|
|-------------|---------|-----------|
|null         |null     |5          |
|Unrated      |Japanese |2          |
|R            |Norwegian|2          |
...

GROUP BY ve ORDER BY

SELECT 
       certification, 
       COUNT(title) AS title_count
FROM films
GROUP BY certification;

|certification|title_count|
|-------------|-----------|
|Unrated      |62         |
|M            |5          |
|G            |112        |
...

SELECT 
       certification, 
       COUNT(title) AS title_count
FROM films
GROUP BY certification
ORDER BY title_count DESC;

|certification|title_count|
|-------------|-----------|
|R            |2118       |
|PG-13        |1462       |
...

Çalışma sırası

-- Yazılan kod:
SELECT 
       certification, 
       COUNT(title) AS title_count
FROM films
GROUP BY certification
ORDER BY title_count DESC
LIMIT 3;

-- Çalışma sırası:

SELECT 
       certification, 
       COUNT(title) AS title_count

FROM films

GROUP BY certification

ORDER BY title_count DESC

LIMIT 3;

Hadi pratik yapalım!

Orta Düzey SQL