Veri gruplama
Oracle SQL'ye Giriş
Hadrien Lacroix
Content Developer
Verileri gruplama
Grup bilgisi
| Composer | Milliseconds |
|-----------------|--------------|
| Antonio Vivaldi | 199,086 |
| Pearl Jam | 122,801 |
| Pearl Jam | 65,593 |
| Jimmy Page | 401,920 |
| Jimmy Page | 386,063 |
| Jimmy Page | 132,702 |
| Jimmy Page | 189,675 |
| Jimmy Page | 126,641 |
| Carlos Santana | 318,432 |
| Carlos Santana | 296,437 |
| Carlos Santana | 882,834 |
| ... | ... |
Her besteci için ortalama parça süresi nedir?
GROUP BY- bir tablodaki satırları gruplara ayırır
- her grup için özet bilgi almak üzere grup fonksiyonlarını kullanır
GROUP BY
SELECT Composer, AVG(Milliseconds)
FROM Track
GROUP BY Composer
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0 |
| Pearl Jam | 94,197.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY ve WHERE
SELECT Composer, AVG(Milliseconds)
FROM Track
WHERE Genre = 1
GROUP BY Composer
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Antonio Vivaldi | 199,086.0 |
| Pearl Jam | 94,197.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY ve ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY AVG(Milliseconds)
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY ve ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY 2
| Composer | AVG(Milliseconds) |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
GROUP BY ve ORDER BY
SELECT Composer, AVG(Milliseconds) AS Average
FROM Track
GROUP BY Composer
ORDER BY Average
| Composer | Average |
|-----------------|-------------------|
| Pearl Jam | 94,197.0 |
| Antonio Vivaldi | 199,086.0 |
| Jimmy Page | 474,888.3 |
| Carlos Santana | 499,234.3 |
| ... | ... |
Yönergeler
SELECT ifadesindeki, toplama fonksiyonu olmayan her sütun veya ifade GROUP BY yan tümcesinde yer almalıdır
SELECT Composer, AVG(Milliseconds), UnitPrice
FROM Track
GROUP BY Composer
column "track.unitprice" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2: SELECT Composer, AVG(Milliseconds), UnitPrice
Yönergeler
SELECT listesindeki, toplama fonksiyonu olmayan her sütun veya ifade GROUP BY yan tümcesinde yer almalıdır
SELECT Composer, AVG(Milliseconds), MAX(UnitPrice)
FROM Track
GROUP BY Composer
| Composer | AVG(Milliseconds) | MAX(UnitPrice) |
|-----------------|-------------------|----------------|
| Antonio Vivaldi | 199,086.0 | 0.99 |
| Pearl Jam | 94,197.0 | 0.99 |
| Jimmy Page | 474,888.3 | 0.99 |
| Carlos Santana | 499,234.3 | 0.99 |
| ... | ... | ... |
Yönergeler
GROUP BY içinde belirtilen ifadelerin SELECT ifadesinde yer alması gerekmez
SELECT AVG(Milliseconds)
FROM Track
GROUP BY Composer
| AVG(Milliseconds) |
|-------------------|
| 199,086.0 |
| 94,197.0 |
| 474,888.3 |
| 499,234.3 |
| ... |
Birden çok sütun
SELECT Country, City, COUNT(CustomerId)
FROM Customer
GROUP BY Country, City
| Country | City | COUNT(CustomerId) |
|-----------|---------------------|-------------------|
| Argentina | Buenos Aires | 1 |
| Australia | Sidney | 1 |
| Austria | Vienne | 1 |
| Belgium | Brussels | 1 |
| Brazil | Brasilia | 1 |
| Brazil | São José dos Campos | 1 |
| Brazil | São Paulo | 2 |
| ... | ... | ... |
Haydi pratik yapalım!
Oracle SQL'ye Giriş
