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

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

वर्तमान 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
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
27 नवम्बर 2019, 22:46