SQL Server ASP.NET C# Kitabı

SUM, AVG, COUNT, MAX ve MIN, null kayıtları görmemezlikten gelir fakat COUNT(*), o kayıtları da dikkate alır. Default olarak ALL sözcüğünün kullanıldığı SUM, AVG ve COUNT fonksiyonlarında, mükerrer kayıtları elemek için isteğe bağlı olarak DISTINCT anahtar sözcüğü, kullanılır. SUM ve AVG fonksiyonları, int, smallint, tinyint, decimal, numeric, float, real, money ve smallmoney gibi sayısal türdeki kolonlar için kullanılır. MIN ve MAX bit türündeki kolonlar için kullanılamaz. COUNT(*) dışındaki fonksiyonları, text ve image data türlerini desteklemez. AVG ve SUM, integer türündeki kolonlar integer düğer döndürür.
Grupsal fonksiyonlar, ORDER BY içerisinde kullanılabilir ancak WHERE cümleciÄŸinde kullanılamaz. Ayrıca bu fonksiyonlar, baÅŸka bir grupsal fonksiyona veya bir subquery’e uygulanamaz.

GROUP BY deyimi, her kayıt grubu için satır satır özet geçmek amacıyla kullanılır. Yani “Sistemdeki satışları maÄŸazalara göre özetlemek”, “Katılımcıları eÄŸitim durumlarına göre gruplamak”,”Hangi ürün grubundan kaçar tane satılmış”,”Hangi maÄŸaza en çok hangi ürün grubunda yüksek ciro yapmış” gibi soruların yanıtları GROUP BY ifadeside saklıdır. Åžimdi örnek tablomuz üzerinde örnekler yapalım.

--Toplam kaç adet satış yapılmış ve bu satışların ortalaması nedir ?
SELECT SUM(Adet) [Satış Adeti], AVG(Adet) [Ortalama Adet] FROM Satis
--Bunun sonucunda Satış Adeti : 36, Ortalama Adet : 5 gelecektir.
--Görüldüğü gibi ortalama int türünde geldi. Oysa ortalama net olarak 5 değildir.
--Bunun için bir casting işlemi yapabiliriz.
SELECT SUM(Adet) [Satış Adeti], AVG(CAST(Adet as numeric(4,2))) [Ortalama Adet] FROM Satis
--Bu durumda Ortalama Adet : 5.142 gelecektir

Her mağaza kaç satış hareketi yapılmış ve toplam adetleri nedir. Bunu da adetlerine göre büyükten küçüğe sıralasın. Burada özetleme mağaza bazında istendiği için GROUP BY kullanacağız.

SELECT Magaza,COUNT(*) [Satış Adeti],SUM(Adet) Toplam
FROM Satis GROUP BY Magaza
ORDER BY 3 DESC --ORDER BY Toplam DESC
--veya
--ORDER BY SUM(Adet) DESC

İçerisinde en az 3 adet ürünün olduğu belgelerin sayılarını mağaza bazında özetleyelim. Burada veri gruplanmadan önce kısıtlanacağı için WHERE kullanılır.

SELECT Magaza,COUNT(*) [Satış Adeti]
FROM Satis WHERE Adet >=3
GROUP BY Magaza

Adet olarak 15′in üzerinde satış yapmış yapmış maÄŸazaları listeyelim. Bu durumda öncelikle maÄŸaza bazında toplam satış adetleri alıp o deÄŸerler üzerinden 15 kriterini girmemiz lazım. Bunun için yani gruplandırılmış veri üzerinde kısıtlama yapmak için HAVING sözcüğü kullanılır. HAVING, GROUP BY’dan sonra yazılım.

SELECT Magaza,SUM(Adet) [Toplam Adet]
FROM Satis
GROUP BY Magaza
HAVING SUM(Adet) >15

Mağaza ve kategori bazında adet toplamlarını getirelim. Yani her mağazada hangi ürün grubu kaç tane satılmış. Bunun için yapacağımız tek şey, tabloyu hem Mağaza hem de Kategori bazında kırmaktır.

SELECT Magaza,Kategori,SUM(Adet) [Toplam Adet]
FROM Satis
GROUP BY Magaza,Kategori
ORDER BY 1,2

ÖrneÄŸimizi biraz daha geliÅŸtirelim; MaÄŸazalarda toplamda en çok satılan ürün gruplarını maÄŸaza bazında gösterelim. Bunun için öncelikle önceki query’i oluÅŸturacağız. Böylece maÄŸaza bazında kategorilerin toplamı gelmiÅŸ olur ardından bu sonuçlarda adetleri yüksek olarak kategorileri, mazaÄŸa seviyesinde listeleyeceÄŸiz. Bunun için subquery oluÅŸturacağız.

SELECT Magaza,MAX(Kategori),MAX(ToplamAdet) FROM(
	SELECT Magaza,Kategori,SUM(Adet) ToplamAdet
	FROM Satis
	GROUP BY Magaza,Kategori
) as T GROUP BY Magaza

Burada güzel bir özellik kullandık. İç tabloyu gruplarken, GROUP BY’a Kategori yazmadık. Ama onu SELECT etmek için yazmak zorundaydık. Fakat en büyük adetli kolonda hep aynı deÄŸer olacağı için bunlardan birini MAX veya MIN ile alabiliriz. Bir anlamda sadece, GROUP BY’a eklenmemiÅŸ kolonun grupsal fonksiyonda kullanılmaz zorunluluÄŸunu aÅŸmak için böyle yaptık. Bu yöntem, özellikle dinamik raporlama araçlarında kolaylıklar saÄŸlamaktadır.

Bu örnekleri verdikten sonra asıl konumuza dönebiliriz;
Verileri özetlerken ara özet veya alt özet satırları oluşturmak için WITH ROLLUP ve WITH CUBE deyimleri kullanılır. ROLLUP ile CUBE operatörleri arasındaki fark şudur;

SELECT Magaza,SUM(Adet)Adet FROM Satis
GROUP BY Magaza
WITH ROLLUP

Görüldüğü gibi hem mağaza bazında toplam satışı hem de tüm toplamları göstermiş oldu. Şimdi hem mağaza hem de kategori bazında ara ve alt özet satırlar oluşturalım.

SELECT Magaza,Kategori,SUM(Adet)Adet FROM Satis
GROUP BY Magaza,Kategori
WITH ROLLUP

Görüldüğü gibi ROLLUP, sağdan sola doğru gruplama yaparak her grup için özet satır oluşturur.

SELECT Sehir,Magaza,Kategori,SUM(Adet) FROM Satis
GROUP BY Sehir,Magaza,Kategori
WITH ROLLUP

kodunu eÄŸer WITH ROLLUP olmaksızın çalıştırırsak SQL Sever, her unique konbinasyon için tek bir özet satır oluÅŸturur. Sehir  Magaza  Kategori  SUM(Adet) çalışma ÅŸekli ÅŸu ÅŸekilde olacaktır. Fakat WITH ROLLUP deyimi kullanıldığı için GROUP BY’da 3 kolon olduÄŸu için saÄŸdan sola doÄŸru ek olarak 3 türlü gruplama özet satırı oluÅŸturur.



CUBE operatörü, ROLLUP gibi özet satırı oluşturur. Tek farkı, GROUP BY deyiminde kullanılmış kolonların değerlerinin bütün kombinasyonları kadar group özeti yapar.

SELECT Magaza,SUM(Adet)Adet FROM Satis
GROUP BY Magaza
WITH CUBE

Bu queryde sadece bir kolona göre gruplama olduÄŸu için ROLLUP’tan farklı bir sonuç getirmedi. Ä°ki kolona göre gruplama yapalım.

SELECT Magaza,Kategori,SUM(Adet)Adet FROM Satis
GROUP BY Magaza,Kategori
WITH CUBE

CUBE operatörünün çalışmasını daha net görmek için 3′lü kırılım yapalım.

SELECT Sehir,Magaza,Kategori,SUM(Adet) FROM Satis
GROUP BY Sehir,Magaza,Kategori
WITH CUBE

Bu durumda aşağıdaki gibi bir gruplama özet çıkarır.

Örneklerimizde SUM fonksiyonunu kullanmamız, ROLLUP veya CUBE’in sadece SUM ile kullanıldığı anlamına gelmiyor. AVG, MAX, MIN veya COUNT fonksiyonlarını da kullanabiliriz.

WITH CUBE veya WITH ROLLUP operatörleri aynı anda en fazla 10 kolon(kırılım noktası) üzerinde özetleme yapabilir.

GROUPING() fonksiyonu
ROLLUP veya CUBE yapılmış özetlemelerde örneklerde gördüğünüz gibi bazı kolonlar NULL değeri içermektedir. Bir kolon, ya tablodaki değeri NULL olduğu için ya da özetlemeden dolayı NULL olarak görünür. Özetlemenin yapıldığı querylerde ihtiyaç duyduğumuz fonksiyonlardan biri olan ve sadece ROLLUP veya CUBE operatörleriyle birlikte kullanılan GROUPING() fonksiyonu bu iki NULL türünü birbirinden ayırmamıza yarar. Daha anlaşılır şekilde söyleyecek olursak; GROUPING() fonksiyonu, parametre olarak aldığı ifadenin sonucu özetlemeden dolayı NULL içeriyorsa yani o satır, parametredeki kolonun özetiyse geriye 1 değilse 0 döndürür. Bunu genellikle, özetlenmiş bir kayıt dizisindeki NULL değerleri yerine TOPLAM ifadesini yazdırmak için kullanılır.

SELECT Magaza,SUM(Adet)Adet FROM Satis
GROUP BY Magaza
WITH ROLLUP

ifadesi, kayıt dizisinin en altına alt toplamı yazdırır.

Fakat burada görüldüğü gibi en alt toplamı bu şekilde vermek pek anlaşılır bir durum değil onun için query şu şekilde düzelteceğiz.

SELECT (CASE WHEN GROUPING(Magaza)=1 THEN 'TOPLAM' ELSE Magaza END) Magaza,
SUM(Adet)Adet FROM Satis
GROUP BY Magaza
WITH ROLLUP

Aynı şekilde bunu kaç kırılım noktası varsa hepsine uygulayabiliriz.

SELECT (CASE WHEN GROUPING(Magaza)=1 THEN 'TOPLAM' ELSE Magaza END) Magaza,
(CASE WHEN GROUPING(Kategori)=1 THEN 'TOPLAM' ELSE Kategori END) Kategori,
SUM(Adet)Adet FROM Satis
GROUP BY Magaza,Kategori
WITH ROLLUP