Union Abfrage gruppieren

[Hazel4tw1]

Hallo zusammen,

Ich habe mir eine Abfrage gebastel die so aussieht:

SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,
  Sum(CASE WHEN Send_To_Dispatch_Reason IS NULL and ServiceTickets.Service_Organisation_Displayname = 'CSC_NOC' AND ServiceTickets.Status = 'Final Customer solved / informed' THEN 1 ELSE 0 END) AS nocsolved, '' AS completsol, '' AS ausnoc
FROM ServiceTickets
WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
GROUP BY Day(ServiceTickets.Solved_Date_Time)
Union
SELECT Day(ServiceTickets.Creation_Date) AS Tag,
  Sum(CASE WHEN ServiceTickets.CSC_NOC = '1' THEN 1 ELSE 0 END) AS erstellt,'' as nocsolved,'' AS completsol,'' AS ausnoc
FROM ServiceTickets
WHERE Month(ServiceTickets.Creation_Date) = 7 AND Year(ServiceTickets.Creation_Date) = 2014
GROUP BY Day(ServiceTickets.Creation_Date)
union
SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,'' as nocsolved,
  Sum(CASE WHEN CSC_NOC = '1' AND ServiceTickets.Status = 'Final Customer solved / informed' THEN 1 ELSE 0 END) AS completsol,'' AS ausnoc
    FROM ServiceTickets
WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
GROUP BY Day(ServiceTickets.Solved_Date_Time)
union
SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,'' as nocsolved, '' AS completsol,
  Sum(CASE WHEN ServiceTickets.CSC_NOC = '1' and Send_To_Dispatch_Reason IS NOT NULL THEN 1 ELSE 0 END) AS ausnoc
  from ServiceTickets
WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
GROUP BY Day(ServiceTickets.Solved_Date_Time)

Das Ergbniss sieht so aus:




Jetzt möchte ich natürlich die "Tage" nicht immer doppelt haben, und nach kruzem googeln, soll man wohl so Union Abfragen gruppieren können:

select * from (
SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,
  Sum(CASE WHEN Send_To_Dispatch_Reason IS NULL and ServiceTickets.Service_Organisation_Displayname = 'CSC_NOC' AND ServiceTickets.Status = 'Final Customer solved / informed' THEN 1 ELSE 0 END) AS nocsolved, '' AS completsol, '' AS ausnoc
FROM ServiceTickets
WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
GROUP BY Day(ServiceTickets.Solved_Date_Time)
Union
SELECT Day(ServiceTickets.Creation_Date) AS Tag,
  Sum(CASE WHEN ServiceTickets.CSC_NOC = '1' THEN 1 ELSE 0 END) AS erstellt,'' as nocsolved,'' AS completsol,'' AS ausnoc
FROM ServiceTickets
WHERE Month(ServiceTickets.Creation_Date) = 7 AND Year(ServiceTickets.Creation_Date) = 2014
GROUP BY Day(ServiceTickets.Creation_Date)
union
SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,'' as nocsolved,
  Sum(CASE WHEN CSC_NOC = '1' AND ServiceTickets.Status = 'Final Customer solved / informed' THEN 1 ELSE 0 END) AS completsol,'' AS ausnoc
    FROM ServiceTickets
WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
GROUP BY Day(ServiceTickets.Solved_Date_Time)
union
SELECT Day(ServiceTickets.Solved_Date_Time) AS Tag, '' as erstellt,'' as nocsolved, '' AS completsol,
  Sum(CASE WHEN ServiceTickets.CSC_NOC = '1' and Send_To_Dispatch_Reason IS NOT NULL THEN 1 ELSE 0 END) AS ausnoc
  from ServiceTickets
WHERE Month(ServiceTickets.Solved_Date_Time) = 7 AND Year(ServiceTickets.Solved_Date_Time) = 2014
GROUP BY Day(ServiceTickets.Solved_Date_Time)) as test
group by
test.tag,test.erstellt,test.nocsolved,test.completsol,test.ausnoc

Aber das Ergebnis ändert sich dabei nicht, was habe ich denn da falsch verstanden, oder ist dies ganricht möglich?

 

[akretschmer]

Jetzt möchte ich natürlich die "Tage" nicht immer doppelt haben, und nach kruzem googeln, soll man wohl so Union Abfragen gruppieren können:

select * from (...)) as test
group by
test.tag,test.erstellt,test.nocsolved,test.completsol,test.ausnoc

Aber das Ergebnis ändert sich dabei nicht, was habe ich denn da falsch verstanden, oder ist dies ganricht möglich?

Du willst sicherlich nach Tag gruppieren und die anderen Felder summieren. Aber warum tust Du es nicht?

 

[Hazel4tw1]

Möp, manchmal seh ich den Wald vor lauter Bäumen nicht, danke.