SQL Count; 0-Werte anzeigen

[Magic1416]

Hallo,

ich habe folgendes Problem bei einer Abfrage:
Ich habe zwei Tabellen (Location und Computer), die in einer 1:n Beziehung zueinander stehen.
In der Tabelle Location befinden sich Standorte denen jeweils eine gewisse Anzahl an Computern zugeordnet sind. Die Tabelle Computer enthält ein boolsches Feld (isNewClient).

Nun will ich pro Location alle Computer gezählt haben, wo der isNewClient Wert sowohl true als auch false ist.

Der Query hierfür sieht folgendermassen aus:

SELECT     Location.LocationCode, Computer.isNewClient, COUNT(*) AS Count
FROM         Computer INNER JOIN
                      Location ON Computer.L_ID = Location.ID
GROUP BY Computer.isNewClient, Location.LocationCode

Das Ergebnis ist folgendes:

LocationCode     isNewClient    Count
-----------------------------------------------
ABC                    true                25
ABC                    false               6
XYZ                    true                 30

Soweit so gut. Nun zum Problem. Für die Location XYZ gibt es keine Computer mehr, wo der Wert isNewClient auf false steht. Daher fehlt mir folgende Zeile in meinem Ergebnis

XYZ                    false              0

Diese brauche ich aber unbedingt für eine Auswertung, die ich machen muss. Ebenso könnte es Locationen geben, denen noch überhaupt kein Computer zugewiesen ist. In diesem Fall hätte ich gerne folgende Zeilen mit im Ergebnis:

FGH                  true                 0
FGH                  false                0

Hat jemand ne Lösung, wie ich zu einem vollständigen Ergebnis komme ?

Danke
Gruss Magic

 

[bassti]

RIGHT JOIN sollte helfen

 

[Magic1416]

Schön wäre es, wenn es so einfach wäre.
Left, Right oder Full Join. Das Ergebnis ist leider das gleiche.

 

[DerEineDa]

Das kann ich mir kaum vorstellen, dass das Ergebnis mit nem FULL OUTER JOIN noch das gleiche ist. Du hast ja keine WHERE oder HAVING Klausel, also sind definitiv alle Datensätze der beiden Tabellen in der Ergebnismenge enthalten.

Falsch ist nur, dass du COUNT(*) benutzt. Denn das zählt alle Zeilen. Du musst einen bestimmten Spaltennamen angeben, damit dort nur die NICHT-NULL-Werte gezählt werden.

 

[Magic1416]

@DerEineDa
Das Ergebnis mit Full Outer Join war nur zufällig gleich, weil zufällig alle Datensätze in der Tabelle Computer einen Bezug in die Tabelle Location hatten. Löst man Bezüge ist das Ergebnis unterschiedlich.
Ich habe meinen String ein wenig geändert. Aber das eigentliche Problem existiert nach wie vor

SELECT  Location.LocationCode, Computer.isNewClient, COUNT(Computer.isNewClient) 
                      AS Count
FROM         Computer FULL OUTER JOIN
                      Location ON Computer.L_ID = Location.ID
GROUP BY Computer.isNewClient, Location.LocationCode
ORDER BY LocationCode

Das Ergebnis ist folgendes:

Location     isNewClient    Count
NULL          False              3
NULL          True               1
ABC            True               25
ABC            False              6
XYZ             True               30

Wieder fehlt mir für XYZ die Zeile zur Auswertung

XYZ            False               0

Das Problem ist einfach, das mit GROUP BY Computer.isNewClient für die Location XYZ nichts zu gruppieren gibt wenn kein Computer den Wert 'false' aufweist, und deshalb gibt es hierfür einfach keine Reihe zurück.

Ergänzung (20. Oktober 2009)

Also ich habe eine Lösung, welche allerdings nicht sehr performant ist.

SELECT     Location.LocationCode, Computer.isNewClient, COUNT(*) AS Count
FROM         Computer INNER JOIN
                      Location ON Computer.L_ID = Location.ID
GROUP BY Location.LocationCode, Computer.isNewClient

UNION ALL

SELECT     TOP (100) PERCENT LocationCode, 'False' AS isNewClient, 0 AS Count
FROM         Location
WHERE     LocationCode NOT IN
                          (SELECT  Distinct   Location.LocationCode
                            FROM          Computer  INNER JOIN
                                                   Location ON Computer.L_ID = Location.ID
                            WHERE      (Computer.isNewClient = 0))
                            
UNION ALL

SELECT     TOP (100) PERCENT LocationCode, 'True' AS isNewClient, 0 AS Count
FROM         Location
WHERE     LocationCode NOT IN
                          (SELECT  Distinct   Location.LocationCode
                            FROM          Computer  INNER JOIN
                                                   Location ON Computer.L_ID = Location.ID
                            WHERE      (Computer.isNewClient = 1))

ORDER BY LocationCode

Der Scheiss liefert zwar ein richtiges Ergebnis, jedoch muss der SQL Server 5 Anweisungen verarbeiten. Je mehr Daten in der Datenbank drin sind desto länger dauert die Abfrage.

 

[Rossibaer]

Hallo zusammen,

auf die Gefahr hin das du das auch schon mal getestet hast, hier eine geänderte SQL Abfrage:

SELECT  
  Location.LocationCode, 
  ISNULL(Computer.isNewClient,0) AS isNewClient,
  COUNT(ISNULL(Computer.isNewClient,0)) AS Count
FROM Computer
RIGHT OUTER JOIN Location ON Computer.L_ID = Location.ID
GROUP BY Location.LocationCode, ISNULL(Computer.isNewClient,0)
ORDER BY LocationCode

Habe lediglich ISNULL eingesetzt und die Reihenfolge im Group By getauscht. Das erschien mir etwas logischer. Schau mal ob das den Trick tut, kann momentan das Ganze leider nicht selber testen, da ich zur Zeit kein MS SQLServer installiert habe.

EDIT Wenn ich es mir recht überlege, haben sowohl du als auch DerEineDa recht. Der Outer Join liefert nur dann eine Zeile für Location wenn entweder 1 Datensatz in Computer vorhanden ist oder wenn kein Datensatz vorhanden ist. Weil aber beim Standort XYZ bereits mind. 1 Datensatz in Computer vorhanden ist, der isNewClient = True stehen hat, sieht das DBMS darin die JOIN Clausel bereits erfüllt und "erfindet" somit keine neue Zeile, die es dann über den OUTER JOIN zusätzlich ausgeben muss.

EDIT 2:
Vergiss die vorige SQL Abfrage von mir, denke das du mit folgendem Kommando mehr Erfolg haben wirst:

SELECT
  Location.LocationCode, 
  Location.isNewClient AS isNewClient,
  COUNT(Computer.isNewClient) AS Count
FROM Computer
RIGHT OUTER JOIN (SELECT ID,LocationCode,1 AS isNewClient FROM Location
UNION SELECT ID,LocationCode,0 FROM Location) AS Location ON Computer.L_ID=Location.ID AND Computer.isNewClient=Location.isNewClient
GROUP BY Location.LocationCode, Location.isNewClient
ORDER BY LocationCode

Zumindest ist man da statt 5 Abfragen auf 3 runter. Der Trick dabei ist statt die Location direkt zu verwenden erst eine "temporäre" Tabelle aus einem UNION Select zu bilden und diese dann für den Join zu verwenden. Performance musst du allerdings prüfen. Ich konnte hier nur mit 3 Datensätzen in Location "rumspielen".

Viel Erfolg!

 

[Magic1416]

@RossiBaer

Deine Abfrage funktioniert ! Und zwar erscheint das Ergebnis auf Knopfdruck bei 16000 Clients in der Tabelle Computer und 96 Einträgen in der Tabelle Locations. Bei meiner Variante hat das etwa gefühlt eine Sekunde lang gedauert trotz Indizies.
Auf die Idee, es so zu machen, wie Du, bin ich überhaupt noch nicht gekommen.
Vielen Dank.

Gruss Magic

 

[Rossibaer]

@Magic: Ok, freut mich. Ich hatte schon Bedenken, wegen der Performance, aber scheinbar sind die Indizes bei dir goldrichtig.

Grüße Rossibär