15. januar 2009 - 21:25Der er
14 kommentarer og 2 løsninger
Problem med Sub total
Hej, Jeg har lavet en kontoopgørelse, der sammentæller en subtotal pr. userid og laver en grand total. Det sidste virker ikke, og det kan jeg også undvære.
Det mærkelige er at subtotal fungerer fint - for det meste - noglegange bliver den dog sat til 0. Jeg kan simpelthen ikke se hvorfor. Fremgangsmåden er fundet efter noget research på nettet, men jeg er åben for alt der kan det samme...
SQL-script ---------- SELECT id, username, is_deleted, is_freeOfCharge, is_invoiced, price, CASE WHEN id = (SELECT TOP 1 id FROM clj_calendar WHERE username = O.username AND is_freeOfCharge = 0 AND is_invoiced = 0 ORDER BY id DESC) THEN (SELECT SUM(price) FROM clj_calendar WHERE id <= O.id AND username = O.username AND is_freeOfCharge = 0 AND is_invoiced = 0 AND start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102) AND end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102)) ELSE ' ' END AS 'Sub Total', CASE WHEN id = (SELECT TOP 1 id FROM clj_calendar ORDER BY username DESC) THEN (SELECT SUM(price) FROM clj_calendar WHERE is_FreeOfCharge = 0 AND is_invoiced =0 AND start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102) AND end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102)) ELSE ' ' END AS 'Grand Total', location, start_time, end_time FROM clj_calendar AS O WHERE (is_freeOfCharge = 0 AND is_invoiced = 0) AND (start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) AND (end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102)) ORDER BY username, 'Sub Total'
RESULTAT (jeg har fjerne datofelterne, da de fylder for meget):
id is_deleted is_invoiced Sub Total Location username is_freeOfcharge price Grand total
En select sum(price) as total ... alene på 0187 giver totalen: 230. Det svarer til summen af de linier der også vises i min query, men subtotalen tælles blot ikke op.
Har lige slanket mit script ved at skære "Grand total" delen væk. Resultatet er det samme:
SELECT id, username, is_deleted, is_freeOfCharge, is_invoiced, price, CASE WHEN id = (SELECT TOP 1 id FROM clj_calendar WHERE username = O.username AND is_freeOfCharge = 0 AND is_invoiced = 0 ORDER BY id DESC) THEN (SELECT SUM(price) FROM clj_calendar WHERE id <= O.id AND username = O.username AND is_freeOfCharge = 0 AND is_invoiced = 0 AND start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102) AND end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102)) ELSE ' ' END AS 'Sub Total'
FROM clj_calendar AS O WHERE (is_freeOfCharge = 0 AND is_invoiced = 0) AND (start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) AND (end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102)) ORDER BY username, 'Sub Total'
Jeg har lidt svært ved at følge med i dit script, så jeg håber at det er ok at jeg prøve at angribe det på ny? Jeg har lige et spørgsmål først.
1) Hvorfor denne CASE...END inden din sub-total-subquery?
CASE WHEN id = (SELECT TOP 1 id FROM clj_calendar WHERE username = O.username AND is_freeOfCharge = 0 AND is_invoiced = 0 ORDER BY id DESC) THEN (SELECT SUM(price) ... /thomas
Det slår mig lige at man også kan lave SQL-sætningen som et JOIN. Altså først udregner med totalsummerne, inkl. maxId pr. bruger og så joiner man det til den oprindelige datatabel (her som tabel-alias "T"):
SELECT O.id, O.username, O.price, COALESCE(T.subtotal) as 'subtotal' FROM clj_calendar O LEFT JOIN (
SELECT MAX(o2.id) as 'MaxId', o2.username, SUM(o2.price) AS 'subtotal' FROM clj_calendar AS O2 WHERE O2.is_freeOfCharge = 0 AND O2.is_invoiced = 0 AND O2.start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102) AND O2.end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102)) GROUP BY o2.username
) T ON T.Maxid = O.id
WHERE (O.is_freeOfCharge = 0 AND O.is_invoiced = 0) AND (O.start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) AND (O.end_time <= CONVERT(DATETIME,'2009-01-31 23:59:00', 102))
ORDER BY O.username ASC, O.id ASC
Jeg har ikke testet sætningen, så jeg kan ikke afvise at der er kommafejl el. lign. /Thomas
Hej Thomas .. jeg har prøvet dit første eksempel. Det fejler med: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 7 Incorrect syntax near ')'. ... den fejler ved anden SELECT sætning.
Det andet eksempel giver mig følgende (efter din rettelse): Msg 156, Level 15, State 1, Line 20 Incorrect syntax near the keyword 'GROUP'.
1) Hvorfor denne CASE...END inden din sub-total-subquery?
Jeg har jo hentet scriptet på et site, men jeg forstår det sådan at for hver gang id'en skifter i første gennemløb, skal der afsluttes med en subtotal. Eller omvendt, for hvert id, gennemløbes tabellen igen og der summeres til subtotal hvis id'en er den samme.
Jeg er enig. Det opdagede jeg da jeg var ved at konstruere mit svar. Tror det er - performancemæssigt er meget dyrere end at lave den løsning som jeg har lavet. Det kan du jo lige tjekke.
Jeg har rettet fejlen. Der var en parantes for meget inden "GROUP BY"
Prøv i stedet for:
SELECT O.id, O.username, O.price, COALESCE(T.subtotal,0) as 'subtotal' FROM clj_calendar O LEFT JOIN ( SELECT MAX(o2.id) as 'MaxId', o2.username, SUM(o2.price) AS 'subtotal' FROM clj_calendar AS O2 WHERE O2.is_freeOfCharge = 0 AND O2.is_invoiced = 0 AND O2.start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102) AND O2.end_time <= CONVERT(DATETIME, '2009-01-31 23:59:00', 102) GROUP BY o2.username
) T ON T.Maxid = O.id
WHERE (O.is_freeOfCharge = 0 AND O.is_invoiced = 0) AND (O.start_time >= CONVERT(DATETIME, '2009-01-01 00:00:00', 102)) AND (O.end_time <= CONVERT(DATETIME,'2009-01-31 23:59:00', 102))
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
