Yardım İL BAZINDA TOPLAM SATIŞLAR

SELECT
SUBSTRING(C.CODE,8,2) 'İL',
L.AMOUNT 'MİKTAR'

FROM
LG_001_01_STLINE L INNER JOIN
LG_001_ITEMS I ON L.STOCKREF = I.LOGICALREF JOIN
LG_001_CLCARD C ON L.CLIENTREF = C.LOGICALREF JOIN
LG_001_01_INVOICE IV ON IV.LOGICALREF = L.INVOICEREF WHERE (L.TRCODE IN (8))
AND I.CARDTYPE =12

;WITH SEHIR AS
(SELECT '01' plaka, N'Adana'  as adı UNION ALL
SELECT '02' ,N'Adıyaman' UNION ALL
SELECT '03' ,N'Afyon' UNION ALL
SELECT '04' ,N'Ağrı' UNION ALL
SELECT '05' ,N'Amasya' UNION ALL
SELECT '06' ,N'Ankara' UNION ALL
SELECT '07' ,N'Antalya' UNION ALL
SELECT '08' ,N'Artvin' UNION ALL
SELECT '09' , N'Aydın' UNION ALL
SELECT '10' , N'Balıkesir' UNION ALL
SELECT '11' , N'Bilecik' UNION ALL
SELECT '12' , N'Bingöl' UNION ALL
SELECT '13' , N'Bitlis' UNION ALL
SELECT '14' , N'Bolu' UNION ALL
SELECT '15' , N'Burdur' UNION ALL
SELECT '16' , N'Bursa' UNION ALL
SELECT '17' , N'Çanakkale' UNION ALL
SELECT '18' , N'Çankırı' UNION ALL
SELECT '19' , N'Çorum' UNION ALL
SELECT '20' , N'Denizli' UNION ALL
SELECT '21' , N'Diyarbakır' UNION ALL
SELECT '22' , N'Edirne' UNION ALL
SELECT '23' , N'Elazığ' UNION ALL
SELECT '24' , N'Erzincan' UNION ALL
SELECT '25' , N'Erzurum' UNION ALL
SELECT '26' , N'Eskişehir' UNION ALL
SELECT '27' , N'Gaziantep' UNION ALL
SELECT '28' , N'Giresun' UNION ALL
SELECT '29' , N'Gümüşhane' UNION ALL
SELECT '30' , N'Hakkari' UNION ALL
SELECT '31' , N'Hatay' UNION ALL
SELECT '32' , N'Isparta' UNION ALL
SELECT '33' , N'Mersin' UNION ALL
SELECT '34' , N'İstanbul' UNION ALL
SELECT '35' , N'İzmir' UNION ALL
SELECT '36' , N'Kars' UNION ALL
SELECT '37' , N'Kastamonu' UNION ALL
SELECT '38' , N'Kayseri' UNION ALL
SELECT '39' , N'Kırklareli' UNION ALL
SELECT '40' , N'Kırşehir' UNION ALL
SELECT '41' , N'Kocaeli' UNION ALL
SELECT '42' , N'Konya' UNION ALL
SELECT '43' , N'Kütahya' UNION ALL
SELECT '44' , N'Malatya' UNION ALL
SELECT '45' , N'Manisa' UNION ALL
SELECT '46' , N'K.Maraş' UNION ALL
SELECT '47' , N'Mardin' UNION ALL
SELECT '48' , N'Muğla' UNION ALL
SELECT '49' , N'Muş' UNION ALL
SELECT '50' , N'Nevşehir' UNION ALL
SELECT '51' , N'Niğde' UNION ALL
SELECT '52' , N'Ordu' UNION ALL
SELECT '53' , N'Rize' UNION ALL
SELECT '54' , N'Sakarya' UNION ALL
SELECT '55' , N'Samsun' UNION ALL
SELECT '56' , N'Siirt' UNION ALL
SELECT '57' , N'Sinop' UNION ALL
SELECT '58' , N'Sivas' UNION ALL
SELECT '59' , N'Tekirdağ' UNION ALL
SELECT '60' , N'Tokat' UNION ALL
SELECT '61' , N'Trabzon' UNION ALL
SELECT '62' , N'Tunceli' UNION ALL
SELECT '63' , N'Şanlıurfa' UNION ALL
SELECT '64' , N'Uşak' UNION ALL
SELECT '65' , N'Van' UNION ALL
SELECT '66' , N'Yozgat' UNION ALL
SELECT '67' , N'Zonguldak' UNION ALL
SELECT '68' , N'Aksaray' UNION ALL
SELECT '69' , N'Bayburt' UNION ALL
SELECT '70' , N'Karaman' UNION ALL
SELECT '71' , N'Kırıkkale' UNION ALL
SELECT '72' , N'Batman' UNION ALL
SELECT '73' , N'Şırnak' UNION ALL
SELECT '74' , N'Bartın' UNION ALL
SELECT '75' , N'Ardahan' UNION ALL
SELECT '76' , N'Iğdır' UNION ALL
SELECT '77' , N'Yalova' UNION ALL
SELECT '78' , N'Karabük' UNION ALL
SELECT '79' , N'Kilis' UNION ALL
SELECT '80' , N'Osmaniye' UNION ALL
SELECT '81' , N'Düzce' ),
LISTE AS (
SELECT
SUBSTRING(C.CODE,8,2) 'İL',
(SELECT ADI FROM SEHIR as f WHERE F.PLAKA =SUBSTRING(C.CODE,8,2) ) [İl Adı],
L.AMOUNT 'MİKTAR'


FROM
LG_006_01_STLINE L INNER JOIN
LG_006_ITEMS I ON L.STOCKREF = I.LOGICALREF JOIN
LG_006_CLCARD C ON L.CLIENTREF = C.LOGICALREF JOIN
LG_006_01_INVOICE IV ON IV.LOGICALREF = L.INVOICEREF WHERE (L.TRCODE IN (8))
AND I.CARDTYPE =12)




SELECT İL,[İl Adı],SUM(MİKTAR) MIKTAR FROM LISTE GROUP BY İL,[İl Adı]