Kompleks/Gelişmiş MYSQL/SQL Sorguları

Aşağıdaki sql sorgusu payment tablosundaki son altı ayın satışlarını aylık olarak listeler.

// SAKILA veritabanı, payment tablosu
SELECT  
	SUM( IF( DATE_FORMAT(payment_date,'%Y-%m')  = DATE_FORMAT(CURDATE(),'%Y-%m') , amount, 0 ) ) 'Ay 1',
	SUM( IF( DATE_FORMAT(payment_date,'%Y-%m')  = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m'), amount, 0 ) ) 'Ay 2',
	SUM( IF( DATE_FORMAT(payment_date,'%Y-%m')  = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH),'%Y-%m'), amount, 0 ) ) 'Ay 3',
	SUM( IF( DATE_FORMAT(payment_date,'%Y-%m')  = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH),'%Y-%m'), amount, 0 ) ) 'Ay 4',
	SUM( IF( DATE_FORMAT(payment_date,'%Y-%m')  = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH),'%Y-%m'), amount, 0 ) ) 'Ay 5',
	SUM( IF( DATE_FORMAT(payment_date,'%Y-%m')  = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH),'%Y-%m'), amount, 0 ) ) 'Ay 6'
FROM payment
ORDER BY payment_date DESC 

Yukarıdaki sql sorgusunu çalıştırdığımızda, aşağıdaki gibi bir sonuç elde etmiş oluruz.

Bu haftanın satışlarını bulmak için,

SELECT  
    SUM(amount) as 'Bu hafta'
FROM payment
WHERE YEARWEEK(payment_date) = YEARWEEK(CURDATE())
ORDER BY payment_date 

Bu ayın satışlarını bulmak için,

SELECT  
    SUM(amount) as 'Bu Ay'
FROM payment
WHERE DATE_FORMAT(payment_date,'%Y-%m')=DATE_FORMAT(CURDATE(),'%Y-%m')
ORDER BY payment_date 

Bu yılın satışlarını bulmak için,

SELECT  
    SUM(amount) as 'Bu Yıl'
FROM payment
WHERE DATE_FORMAT(payment_date,'%Y')=DATE_FORMAT(CURDATE(),'%Y')
ORDER BY payment_date 

Bu ay ve önceki ayın satışlarını bulmak için,

SELECT  
    SUM( IF( DATE_FORMAT(payment_date,'%Y-%m') = DATE_FORMAT(CURDATE(),'%Y-%m'), amount, 0 ) ) 'Bu Ay',
    SUM( IF( DATE_FORMAT(payment_date,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m'), amount, 0 ) ) 'Önceki Ay'
FROM payment

Son 10 satışı bulmak için,

SELECT payment_date, amount
FROM payment
ORDER BY payment_date DESC 
LIMIT 10

En yüksek 10 satışı bulmak için,

SELECT payment_date, max(amount) as fiyat
FROM payment
GROUP BY payment_date
ORDER BY fiyat DESC 
LIMIT 10

En düşük 10 satışı bulmak için,

SELECT payment_date, min(amount) as fiyat
FROM payment
GROUP BY payment_date
ORDER BY fiyat ASC
LIMIT 10 /* 10 sayısı ilk 10 satışı verir. 15 olarak değiştirdimizde, en düşük 15 satışı bulmuş oluruz*/

Bu ayın en yüksek 10 satışını bulmak için,

SELECT payment_date,amount 
FROM payment 
WHERE MONTH(payment_date) = MONTH(CURDATE()) AND YEAR(payment_date)  =  YEAR(CURDATE()) AND YEAR(payment_date) = YEAR(CURDATE())
GROUP BY payment_date
ORDER BY SUM(amount) DESC
LIMIT 5

Müşteriye göre satışların toplamını, satışların ortalamasını ve satış adetinin bulunması için,

SELECT CONCAT(c.first_name,' ', c.last_name) as isim,
       SUM(p.amount) AS `Toplam`, 
       AVG(p.amount) as 'Averaj',
       COUNT(*) as 'Adet' FROM payment p
INNER JOIN customer c ON p.customer_id = c.customer_id 
GROUP BY isim 
ORDER BY `Toplam` DESC 
LIMIT 10

Günlere göre satış adetini bulmak,

Aşağıdaki sorgu Son 30 Gün, 30 ile 60 gün arası, 60 ile 90 gün arası ve 90 günden önceki satış adetlerini bulmak için kullanılabilir. Ufak bır değişlikle adet yerine satış toplam değerleride bulunabilir. Örneğin, SUM (IF’ deki 1 sayısı yerine amount yazıldığında satışların toplamını elde edersiniz.

SELECT  
       SUM( IF(  p.payment_date > DATE_SUB( SYSDATE( ) ,INTERVAL 30 DAY), 1, 0 ) ) 'Son 30 Gün',
       SUM( IF(  p.payment_date  BETWEEN DATE_SUB( SYSDATE( ) ,INTERVAL 60 DAY) AND DATE_SUB( SYSDATE( ) ,INTERVAL 30 DAY), 1, 0 ) ) '30-60 Gün', 
       SUM( IF(  p.payment_date BETWEEN DATE_SUB( SYSDATE( ) ,INTERVAL 90 DAY ) AND DATE_SUB( SYSDATE( ) ,INTERVAL 60 DAY ), 1, 0 ) ) '60-90 Gün', 
       SUM( IF(  p.payment_date <= DATE_SUB(SYSDATE(), INTERVAL 90 DAY), 1, 0 ) ) '90 Gün öncesi'
FROM customer c
INNER JOIN payment p ON  p.customer_id = c.customer_id 

Bir sorgu içinde bütün satışları, bu hafta, ay ve yıl içindeki bütün satışları bul,

SELECT Round(sum(d.amount),2) as 'Bütün Satışlar', 
    ( SELECT Round(sum(b.amount),2)
           FROM payment b
          WHERE YEARWEEK(b.payment_date) = YEARWEEK(CURRENT_DATE)
    ) AS 'Bu Hafta',
    ( SELECT Round(sum(c.amount),2)
           FROM payment c
          WHERE  date_format(c.`payment_date`, '%Y-%m') = date_format(now(), '%Y-%m')
    ) AS 'Bu Ay',
    ( SELECT Round(sum(d.amount),2)
           FROM payment d
          WHERE  date_format(d.`payment_date`, '%Y') = date_format(now(), '%Y')
    ) AS 'Bu Yıl'
FROM payment d

Yıllık, aylara göre toplam satışları ve satış adetini bul,

SELECT  
         IfNull(Year,'Toplam') `Yıl`,  
	  `Ocak`, `Şubat`, `Mart`, `Nisan`, `Mayıs`, `Haziran`, `Temmuz`, `Ağustos`, `Eylül`, `Ekim`, `Kasım`, `Aralık`,  
	  Qty AS 'Satış Adedi',  
	  Yrly as 'Yıllık Toplam' 
FROM ( 
  SELECT  
         year(payment_date) AS 'Year', 
         Round(Sum(CASE WHEN Month(payment_date)= 1 THEN amount ELSE 0 END),2) AS `Ocak`, 
         Round(Sum(CASE WHEN Month(payment_date)= 2 THEN amount ELSE 0 END),2) AS `Şubat`, 
         Round(Sum(CASE WHEN Month(payment_date)= 3 THEN amount ELSE 0 END),2) AS `Mart`, 
         Round(Sum(CASE WHEN Month(payment_date)= 4 THEN amount ELSE 0 END),2) AS `Nisan`, 
         Round(Sum(CASE WHEN Month(payment_date)= 5 THEN amount ELSE 0 END),2) AS `Mayıs`, 
         Round(Sum(CASE WHEN Month(payment_date)= 6 THEN amount ELSE 0 END),2) AS `Haziran`, 
         Round(Sum(CASE WHEN Month(payment_date)= 7 THEN amount ELSE 0 END),2) AS `Temmuz`, 
         Round(Sum(CASE WHEN Month(payment_date)= 8 THEN amount ELSE 0 END),2) AS `Ağustos`, 
         Round(Sum(CASE WHEN Month(payment_date)= 9 THEN amount ELSE 0 END),2) AS `Eylül`, 
         Round(Sum(CASE WHEN Month(payment_date)=10 THEN amount ELSE 0 END),2) AS `Ekim`, 
         Round(Sum(CASE WHEN Month(payment_date)=11 THEN amount ELSE 0 END),2) AS `Kasım`, 
         Round(Sum(CASE WHEN Month(payment_date)=12 THEN amount ELSE 0 END),2) AS `Aralık`, 
         Count(*) AS Qty, 
         Round(Sum(amount),2) AS Yrly 
   FROM payment 
   GROUP BY year  
  /*WITH ROLLUP  - with rollup, tablonun son satırında satışların toplam degerlerini listeler. Pivot table*/
) AS sums

PHP kullanarak aşağıdaki gibi bir tablo oluşturabilirsiniz.

Çeyrek içindeki bütün satışların toplamını, satışların adetini bul,

SELECT C.yil, C.ceyrek, C.adet,C.toplam
FROM payment AS Q,
     ( SELECT YEAR(payment_date) AS yil, /*yıl*/
            FLOOR( ( MONTH(payment_date) - 1 ) / 3 ) AS ceyrek, /*çeyrek*/
            COUNT(*) AS adet, /*toplam satış adeti*/
            SUM(amount) as toplam /*toplam satış*/
       FROM payment
       GROUP BY YEAR(payment_date), FLOOR( ( MONTH(payment_date) - 1 ) / 3 )
    ) AS C
GROUP BY C.ceyrek
ORDER BY C.yil, C.ceyrek