Wie man leere Gruppen in der SQL GROUP BY-Klausel zurückgibt

Ich habe eine Abfrage, um zurückzukehren, wie viel auf Vertrag und Off-Vertrag an jedem Ort ausgegeben wird, das so etwas zurückgibt:

Location | ContractStatus | Expenses -------------+----------------+--------- New York | Ad-hoc | 2043.47 New York | Contracted | 2894.57 Philadelphia | Ad-hoc | 3922.53 Seattle | Contracted | 2522.00 

Das Problem ist, ich bekomme nur eine Zeile für Standorte, die alle ad-hoc oder alle vertraglich vereinbarten Ausgaben sind. Ich würde gern zwei Reihen zurück für jeden Standort, wie folgt:

 Location | ContractStatus | Expenses -------------+----------------+--------- New York | Ad-hoc | 2043.47 New York | Contracted | 2894.57 Philadelphia | Ad-hoc | 3922.53 Philadelphia | Contracted | 0.00 Seattle | Ad-hoc | 0.00 Seattle | Contracted | 2522.00 Standort | Location | ContractStatus | Expenses -------------+----------------+--------- New York | Ad-hoc | 2043.47 New York | Contracted | 2894.57 Philadelphia | Ad-hoc | 3922.53 Philadelphia | Contracted | 0.00 Seattle | Ad-hoc | 0.00 Seattle | Contracted | 2522.00 

Gibt es irgendeine Art, wie ich dies durch SQL erreichen kann? Hier ist die eigentliche Abfrage, die ich verwende (SQL server 2005):

 SELECT Location, CASE WHEN Orders.Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END AS ContractStatus, SUM(OrderTotal) AS Expenses FROM Orders GROUP BY Location, CASE WHEN Orders.Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END ORDER BY Location ASC, ContractStatus ASC 

Ja, konstruiere einen Ausdruck, der das ordertotal nur für adhoc zurückgibt, und 0 für die anderen und ein anderes, das das Gegenteil tut und diese Ausdrücke summiert. Dies wird eine Zeile pro Standort mit zwei Spalten eine für adhoc und eine für vertraglich …

  SELECT Location, Sum(Case When Contract_ID Is Null Then OrderTotal Else 0 End) AdHoc, Sum(Case When Contract_ID Is Null Then 0 Else OrderTotal End) Contracted FROM Orders GROUP BY Location 

Wenn Sie reallly getrennte Reihen für jedes wünschen, dann würde ein Ansatz zu sein:

  SELECT Location, Min('AdHoc') ContractStatus, Sum(Case When Contract_ID Is Null Then OrderTotal Else 0 End) OrderTotal FROM Orders GROUP BY Location Union SELECT Location, Min('Contracted') ContractStatus, Sum(Case When Contract_ID Is Null Then 0 Else OrderTotal End) OrderTotal FROM Orders GROUP BY Location Order By Location 

Sie können COALESCE , um 0 für NULL Werte zurückzugeben:

 SELECT Orders.Location , (CASE WHEN Orders.Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END) AS ContractStatus , COALESCE(SUM(Orders.OrderTotal), 0.00) AS Expenses FROM Orders GROUP BY Orders.Location , (CASE WHEN Orders.Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END) ORDER BY Orders.Location ASC, ContractStatus ASC 

Sie müssen zuerst eine list von verschiedenen Orten und Verträgen füllen. Sehen Sie diese Abfrage, es könnte einige Syntaxerrors haben, da ich es nicht ausgeführt habe.

 ;WITH Locations AS ( SELECT DISTINCT Location, 1 as ContractStatus FROM Orders UNION SELECT DISTINCT Location, 2 as ContractStatus FROM Orders ) SELECT Location, CASE WHEN L.ContractStatus = 1 THEN 'Ad-hoc' ELSE 'Contracted' END, ISNULL(SUM(OrderTotal),0) Expenses FROM Locations L LEFT JOIN Orders O ON L.Location = O.Location AND L.ContractStatus = CASE WHEN O.Contract_ID IS NULL THEN 1 ELSE 2 END GROUP BY L.Location, CASE WHEN L.ContractStatus = 1 THEN 'Ad-hoc' ELSE 'Contracted' END ( ;WITH Locations AS ( SELECT DISTINCT Location, 1 as ContractStatus FROM Orders UNION SELECT DISTINCT Location, 2 as ContractStatus FROM Orders ) SELECT Location, CASE WHEN L.ContractStatus = 1 THEN 'Ad-hoc' ELSE 'Contracted' END, ISNULL(SUM(OrderTotal),0) Expenses FROM Locations L LEFT JOIN Orders O ON L.Location = O.Location AND L.ContractStatus = CASE WHEN O.Contract_ID IS NULL THEN 1 ELSE 2 END GROUP BY L.Location, CASE WHEN L.ContractStatus = 1 THEN 'Ad-hoc' ELSE 'Contracted' END ) ;WITH Locations AS ( SELECT DISTINCT Location, 1 as ContractStatus FROM Orders UNION SELECT DISTINCT Location, 2 as ContractStatus FROM Orders ) SELECT Location, CASE WHEN L.ContractStatus = 1 THEN 'Ad-hoc' ELSE 'Contracted' END, ISNULL(SUM(OrderTotal),0) Expenses FROM Locations L LEFT JOIN Orders O ON L.Location = O.Location AND L.ContractStatus = CASE WHEN O.Contract_ID IS NULL THEN 1 ELSE 2 END GROUP BY L.Location, CASE WHEN L.ContractStatus = 1 THEN 'Ad-hoc' ELSE 'Contracted' END 

Sie müssen irgendwie mindestens eine Zeile jedes Status für jeden Ort vor der Gruppierung erstellen. Hier ist ein path:

 SELECT s.Location, s.ContractStatus, ISNULL(o.Expenses, 0) AS Expenses FROM ( SELECT Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END AS ContractStatus, SUM(OrderTotal) AS Expenses FROM Orders GROUP BY Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END) ) o RIGHT JOIN ( SELECT DISTINCT o.Location, s1.ContractStatus FROM Orders o CROSS JOIN ( SELECT 'Ad-hoc' AS ContractStatus UNION ALL SELECT 'Contracted' ) s1 ) s ON s.Location = o.Location AND s.ContractStatus = o.ContractStatus ( SELECT s.Location, s.ContractStatus, ISNULL(o.Expenses, 0) AS Expenses FROM ( SELECT Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END AS ContractStatus, SUM(OrderTotal) AS Expenses FROM Orders GROUP BY Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END) ) o RIGHT JOIN ( SELECT DISTINCT o.Location, s1.ContractStatus FROM Orders o CROSS JOIN ( SELECT 'Ad-hoc' AS ContractStatus UNION ALL SELECT 'Contracted' ) s1 ) s ON s.Location = o.Location AND s.ContractStatus = o.ContractStatus RECHTS JOIN SELECT s.Location, s.ContractStatus, ISNULL(o.Expenses, 0) AS Expenses FROM ( SELECT Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END AS ContractStatus, SUM(OrderTotal) AS Expenses FROM Orders GROUP BY Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END) ) o RIGHT JOIN ( SELECT DISTINCT o.Location, s1.ContractStatus FROM Orders o CROSS JOIN ( SELECT 'Ad-hoc' AS ContractStatus UNION ALL SELECT 'Contracted' ) s1 ) s ON s.Location = o.Location AND s.ContractStatus = o.ContractStatus ( SELECT s.Location, s.ContractStatus, ISNULL(o.Expenses, 0) AS Expenses FROM ( SELECT Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END AS ContractStatus, SUM(OrderTotal) AS Expenses FROM Orders GROUP BY Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END) ) o RIGHT JOIN ( SELECT DISTINCT o.Location, s1.ContractStatus FROM Orders o CROSS JOIN ( SELECT 'Ad-hoc' AS ContractStatus UNION ALL SELECT 'Contracted' ) s1 ) s ON s.Location = o.Location AND s.ContractStatus = o.ContractStatus CROSS JOIN SELECT s.Location, s.ContractStatus, ISNULL(o.Expenses, 0) AS Expenses FROM ( SELECT Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END AS ContractStatus, SUM(OrderTotal) AS Expenses FROM Orders GROUP BY Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END) ) o RIGHT JOIN ( SELECT DISTINCT o.Location, s1.ContractStatus FROM Orders o CROSS JOIN ( SELECT 'Ad-hoc' AS ContractStatus UNION ALL SELECT 'Contracted' ) s1 ) s ON s.Location = o.Location AND s.ContractStatus = o.ContractStatus ( SELECT s.Location, s.ContractStatus, ISNULL(o.Expenses, 0) AS Expenses FROM ( SELECT Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END AS ContractStatus, SUM(OrderTotal) AS Expenses FROM Orders GROUP BY Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END) ) o RIGHT JOIN ( SELECT DISTINCT o.Location, s1.ContractStatus FROM Orders o CROSS JOIN ( SELECT 'Ad-hoc' AS ContractStatus UNION ALL SELECT 'Contracted' ) s1 ) s ON s.Location = o.Location AND s.ContractStatus = o.ContractStatus ) s SELECT s.Location, s.ContractStatus, ISNULL(o.Expenses, 0) AS Expenses FROM ( SELECT Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END AS ContractStatus, SUM(OrderTotal) AS Expenses FROM Orders GROUP BY Location, CASE WHEN Contract_ID IS NULL THEN 'Ad-hoc' ELSE 'Contracted' END) ) o RIGHT JOIN ( SELECT DISTINCT o.Location, s1.ContractStatus FROM Orders o CROSS JOIN ( SELECT 'Ad-hoc' AS ContractStatus UNION ALL SELECT 'Contracted' ) s1 ) s ON s.Location = o.Location AND s.ContractStatus = o.ContractStatus 

Bearbeiten: Ich bin mir nicht sicher über die performance dieses DISTINCT / CROSS JOIN Combo, aber wenn es eine einmalige oder selten verwendete Abfrage ist, sollte es OK sein.