मुझे विश्वास है कि इस समूह में यह प्रश्न पहली बार पूछा गया है।

एसक्लाइट का उपयोग करके मैं प्रत्येक पंक्ति तल में कुल गिनती के साथ पिवट डिस्प्ले करना चाहता हूं। और मैं पिवट टेबल करने में सक्षम हूं लेकिन ऐसा करने में कुल गिनती विफल रही है।

वर्तमान SQL क्वेरी:

SELECT
    number as no,
	outl as name,
    (CASE WHEN week = "WEEK1" THEN sunday ELSE 0 END) AS WK1S,
    (CASE WHEN week = "WEEK1" THEN monday ELSE 0 END) AS WK1M,
    (CASE WHEN week = "WEEK1" THEN tuesday ELSE 0 END) AS WK1T,
	(CASE WHEN week = "WEEK1" THEN wednesday ELSE 0 END) AS WK1W,
    (CASE WHEN week = "WEEK1" THEN thursday ELSE 0 END) AS WK1T,
    (CASE WHEN week = "WEEK1" THEN saturday ELSE 0 END) AS WK1SA,
	(CASE WHEN week = "WEEK2" THEN sunday ELSE 0 END) AS WK2S,
    (CASE WHEN week = "WEEK2" THEN monday ELSE 0 END) AS WK21M,
	(CASE WHEN week = "WEEK3" THEN sunday ELSE 0 END) AS WK3S,
    (CASE WHEN week = "WEEK3" THEN monday ELSE 0 END) AS WK3M,
    (CASE WHEN week = "WEEK3" THEN tuesday ELSE 0 END) AS WK3T,
	(CASE WHEN week = "WEEK3" THEN wednesday ELSE 0 END) AS WK3W,
    (CASE WHEN week = "WEEK3" THEN thursday ELSE 0 END) AS WK3T,
    (CASE WHEN week = "WEEK3" THEN saturday ELSE 0 END) AS WK3SA
	 
FROM labels51 group by number 

वर्तमान आउटपुट यहां छवि विवरण दर्ज करें

अपेक्षित: नीचे मुझे प्रत्येक पंक्ति की कुल गणना की आवश्यकता है जैसे मामला जब WK1S = रविवार फिर गिनती (WK1S) और मामला जब WK1M = सोमवार फिर गिनती (WK1M) .... ..

नए सुझाव के आधार पर परिणाम ठीक काम कर रहा है।

-1
Leace 26 नवम्बर 2019, 22:56

1 उत्तर

सबसे बढ़िया उत्तर

ये कोशिश करें

SELECT
    number as no,
	outl as name,
		
    (CASE WHEN week = "WEEK1" THEN sunday ELSE 0 END) AS WK1S,
    (CASE WHEN week = "WEEK1" THEN monday  ELSE 0 END) AS WK1M,
    (CASE WHEN week = "WEEK1" THEN tuesday ELSE 0 END) AS WK1T,
	(CASE WHEN week = "WEEK1" THEN wednesday ELSE 0 END) AS WK1W,
    (CASE WHEN week = "WEEK1" THEN thursday ELSE 0 END) AS WK1T,
    (CASE WHEN week = "WEEK1" THEN saturday ELSE 0 END) AS WK1SA,
	(CASE WHEN week = "WEEK2" THEN sunday ELSE 0 END) AS WK2S,
    (CASE WHEN week = "WEEK2" THEN monday ELSE 0 END) AS WK21M,
	(CASE WHEN week = "WEEK3" THEN sunday ELSE 0 END) AS WK3S,
    (CASE WHEN week = "WEEK3" THEN monday ELSE 0 END) AS WK3M,
    (CASE WHEN week = "WEEK3" THEN tuesday ELSE 0 END) AS WK3T,
	(CASE WHEN week = "WEEK3" THEN wednesday ELSE 0 END) AS WK3W,
    (CASE WHEN week = "WEEK3" THEN thursday ELSE 0 END) AS WK3T,
    (CASE WHEN week = "WEEK3" THEN saturday ELSE 0 END) AS WK3SA
	 
FROM labels51 
     
	 UNION 	 all
	 SELECT "GRAND TOTAL",
	NULL ,
	
	  
	  COUNT(CASE WHEN week = "WEEK1" AND sunday LIKE "%sunday%" THEN 1 END) AS WK1S,
    COUNT(CASE WHEN week = "WEEK1" AND monday LIKE "%monday%" THEN 1 END) AS WK1M,
    COUNT(CASE WHEN week = "WEEK1" AND tuesday LIKE "%tuesday%" THEN 1 END) AS WK1T,
	COUNT(CASE WHEN week = "WEEK1" AND wednesday LIKE "%wednesday%" THEN 1 END) AS WK1W,
    COUNT(CASE WHEN week = "WEEK1" AND thursday LIKE "%thursday%" THEN 1 END) AS WK1T,
    COUNT(CASE WHEN week = "WEEK1" AND saturday LIKE "%saturday%" THEN 1 END) AS WK1SA,
	COUNT(CASE WHEN week = "WEEK2" AND sunday LIKE "%sunday%" THEN 1 END) AS WK2S,
   COUNT(CASE WHEN week = "WEEK2" AND monday LIKE "%monday%" THEN 1 END) AS WK2M,
	  COUNT(CASE WHEN week = "WEEK1" AND sunday LIKE "%sunday%" THEN 1 END) AS WK3S,
    COUNT(CASE WHEN week = "WEEK3" AND monday LIKE "%monday%" THEN 1 END) AS WK3M,
    COUNT(CASE WHEN week = "WEEK3" AND tuesday LIKE "%tuesday%" THEN 1 END) AS WK3T,
	COUNT(CASE WHEN week = "WEEK3" AND wednesday LIKE "%wednesday%" THEN 1 END) AS WK3W,
    COUNT(CASE WHEN week = "WEEK3" AND thursday LIKE "%thursday%" THEN 1 END) AS WK3T,
    COUNT(CASE WHEN week = "WEEK3" AND saturday LIKE "%saturday%" THEN 1 END) AS WK3SA

	 FROM labels51 
1
user12376848user12376848 27 नवम्बर 2019, 22:46