Select in Select - wie schreibe ich es besser?

Y

Yufka

Neuer Benutzer
Beiträge
4
Hallo Community,

ich als SQL Laie soll für einen Kollegen eine wohl einfache Auswertung machen.
Leider funktioniert das ganze nicht so wie ich will.
Es sind zwei Tabellen. Einmal soll die Gesamtzahl Pro Location ermittelt werden und einmal die Anzahl der Installationen pro OU mit einem Subselect. Leider wirft das Subselect immer die Gesamtanzahl raus.
Ich denke das Query macht genau was ich geschrieben habe, aber irgendwie weiß ich nicht wie ich es umschreiben könnte:

use Emp***
Select Count(Distinct InvSoftware.client_id) as CountClients, SUBSTRING (name, 3,3) as Location,
(
select Count (Distinct client_id)
from InvSoftware
where ProductName like 'Internet%' and Version like '10.0'
) CountInstallation

from InvSoftware Inner JOIN Clients ON Clients.client_id = InvSoftware.client_id
Group by substring (name, 3,3)


Auszug aus dem Result:
Die CountClients und Location passe. Nur die CountInstallation ist immer Global und nicht Location bezogen.
CountClients Location CountInstallation
------------ -------- -----------------
1 105
1 _CO 105
1 _SC 105
21 -07 105
2 1SQ 105
18 BE1 105
37 BEL 105
54 BER 105
50 BLA 105



Falls jemand nen Tipp hat wäre ich wirklich froh.
 
Werbung:
Hallo,

also es entsteht in MS SQL. Ist ein SQL Server Cluster 2008. Test Daten habe ich angehängt.
Spalte Name war wirklich unübersichtlich. Müsste eigentlich so heißen: SUBSTRING (Clients.name, 3,3)

Also das Konstrukt soll eigentlich folgendes machen: Es liest alle ClientNamen aus der dbo.Clients. Aufgrund unserer Namensvorschriften sind immer die Zeichen 3-5 ein Standort.
Der Substring extrahiert also aus dem Namen ASDM8000 den Standort DM8. Dann zählt er anhand der InvSoftware.client_id wie viele IDs es pro Standort gibt.
CountClients Location CountInstallation
------------ -------- -----------------
25 BI4 105

Das funktioniert. Dann soll er aber noch in der dritten Spalte aufzeigen, wie viele davon den Internet Explorer 10 Installiert haben. Daher kommt der Subselect.
Aber er zählt nur die gesamtmenge und gibt diese dann in jeder Zeile aus.

Ich hoffe mein verwirrendes Geschreibe kann man irgendwie nachvollziehen.
 

Anhänge

  • n testdaten.zip
    9,8 KB · Aufrufe: 6
Code: 
SELECT    t.name AS Location,
        (    SELECT    count(DISTINCT s1.client_id)
            FROM    InvSoftware s1
            WHERE    s1.client_id = Clients.client_id ) AS anzahl_clients,
        (    SELECT    count(DISTINCT s2.client_id)
            FROM    InvSoftware s2
            WHERE    s2.client_id = Clients.client_id
            AND        ProductName LIKE 'Internet%'
            AND        [Version] LIKE '10.0' ) AS anzahl_installationen
FROM    (    SELECT    DISTINCT
                    Clients.client_id,
                    substring(Clients.name,3,3) AS name
            FROM    Clients ) t
Das könnte klappen aber ich bin aber grade auch irgendwie etwas gaga.
 
Das müsste eig. funktionieren... Hab leider grade keine Testmöglichkeit.
(Evtl. musst du die Syntax anpassen... Bin aus der Oracle-Ecke :p )
Code: 
Select contract as location,
       count(client_id) as countclients,
       sum(is_installed) as countinstallation
From   (Select distinct substr(cli.name, 3, 3) as contract,
                        cli.client_id,
                        case
                           when inv.productname is null then 0
                           else 1
                        end as is_installed
        From   clients cli
        Left   Join invsoftware inv
        On     cli.client_id = inv.client_id
        And    inv.productname Like 'Internet%'
        And    inv.version Like '10.0')
Group  By contract
 
Vielen Dank für die Hilfe.
Noch funktioniert es leider nicht.
Beim Query von Ukulele kommt immer:

Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "Clients.client_id" could not be bound.
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "Clients.client_id" could not be bound.

Ich habe es bereits mit weiteren joins und/oder group by versucht. irgendwas meckert er immer.


Bei code von Distrilec habe ich substr(ing) geändert. Dann kommt aber immer die Meldung Incorrect syntax near ***. Entweder wegen des Group am Ende oder wenn man es entfernt die letzte Klammer.

Ich versuche noch etwas weiter. Habe ja Zeit.

Vielen Dank nochmal
 
Sry war ein Versehen:
Code: 
SELECT    t.name AS Location,
        (    SELECT    count(DISTINCT s1.client_id)
            FROM    InvSoftware s1
            WHERE    s1.client_id = t.client_id ) AS anzahl_clients,
        (    SELECT    count(DISTINCT s2.client_id)
            FROM    InvSoftware s2
            WHERE    s2.client_id = t.client_id
            AND        ProductName LIKE 'Internet%'
            AND        [Version] LIKE '10.0' ) AS anzahl_installationen
FROM    (    SELECT    DISTINCT
                    Clients.client_id,
                    substring(Clients.name,3,3) AS name
            FROM    Clients ) t
 
Yufka schrieb:
Bei code von Distrilec habe ich substr(ing) geändert. Dann kommt aber immer die Meldung Incorrect syntax near ***. Entweder wegen des Group am Ende oder wenn man es entfernt die letzte Klammer.
Zum Vergrößern anklicken....
Sagte ich ja... :) Sehe aber nichts was irgendwie geändert werden müsste...

Hier mal für Oracle ->
Code: 
With invsoftware As
(Select 96797 as client_id,   'Microsoft Internet Explorer 10' as productname, '10.0' as version From dual Union All
Select 26215,   'Microsoft Internet Explorer 10', '9.0' From dual Union All
Select 12090,   'Microsoft Internet Explorer 10', '10.0' From dual Union All
Select 57248,   'Microsoft Internet Explorer 10', '10.5' From dual Union All
Select 1608,   'Microsoft Internet Explorer 10', '11.0' From dual Union All
Select 26219,   'Microsoft Internet Explorer 10', '10.0' From dual Union All
Select 12454,   'Microsoft Internet Explorer 10', '10.0' From dual),
clients As (Select 96797 as client_id, 'ABCDEFGHIJKLMN' as name From dual union all
Select 26215 as client_id, 'ABCDEFGH' as name From dual union all
Select 12090 as client_id, 'ABCDEFGH' as name From dual union all
Select 57248 as client_id, 'FGHJJJ' as name From dual union all
Select 1608 as client_id, 'FGHJJJ' as name From dual union all
Select 26219 as client_id, 'FGHJJJ' as name From dual union all
Select 12454 as client_id, 'KLJJFHSFDS' as name From dual)

Select contract as location,
       count(client_id) as countclients,
       sum(is_installed) as countinstallation
From   (Select distinct substr(cli.name, 3, 3) as contract,
                        cli.client_id,
                        case
                           when inv.productname is null then 0
                           else 1
                        end as is_installed
        From   clients cli
        Left   Join invsoftware inv
        On     cli.client_id = inv.client_id
        And    inv.productname Like '%Internet Explorer%'
        And    inv.version = '10.0')
Group  By contract
Result:
Code: 
       LOCATION    COUNTCLIENTS    COUNTINSTALLATION
1    CDE    3    2
2    JJF    1    1
3    HJJ    3    1
 
Mensch seid ihr schnell. Da komm ich ja kaum hinterher. Danke

Also: Beim überarbeiteten Code von Ukulele funktioniert die Auswertung. Er gruppiert mir dies allerdings nicht pro Location.
Code: 
Location    anzahl_clients    anzahl_installationen
LV3    1    0
W09    1    0
W08    1    0
W08    1    0
W08    1    0
W08    1    0
W08    1    0
W08    1    0
W08    1    1

Wenn ich am Ende ein group by t.name hinzufüge, kommt er wieder mit einer anderen Fehlermeldung daher:
Code: 
Msg 8120, Level 16, State 1, Line 4
Column 't.client_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Trotzdem kann ich mit dem Code leben, da ich die Summierung einfach in Excel mit einer Pivot Tabelle hin bekomme.


Den Code von dir Distrilec schau ich mir jetzt im nachgang an. Wenns in Oracle funktioniert dann werd ich es bei MS ja wohl auch zum laufen bekommen.

Vielen Dank auf jeden fall
 
Wenn du aber erst mit Excel gruppierst ist meine Abfrage ziemlicher Unfug. Probier mal noch folgendes:
Code: 
SELECT    location,
        sum((CASE WHEN clients IS NOT NULL THEN 1 ELSE 0 END)) AS anzahl_clients,
        sum((CASE WHEN installationen IS NOT NULL THEN 1 ELSE 0 END)) AS anzahl_installationen
FROM    (

SELECT    t.location,
        s1.client_id AS clients,
        s2.client_id AS installationen
FROM    (    SELECT    DISTINCT
                    Clients.client_id,
                    substring(Clients.name,3,3) AS location
            FROM    Clients ) t
LEFT JOIN InvSoftware s1
ON        s1.client_id = t.client_id
LEFT JOIN InvSoftware s2
ON        s2.client_id = t.client_id
AND        ProductName LIKE 'Internet%'
AND        [Version] LIKE '10.0'
GROUP BY t.location,s1.client_id,s2.client_id

        ) tabelle
GROUP BY location
 
Hi,

sollte eigentlich auch ohne Sub-Select gehen:

Code: 
SELECT
SUBSTRING (name, 3,3) as Location,
COUNT(Distinct InvSoftware.client_id) as CountClients,
COUNT(DISTINCT (CASE WHEN ProductName like 'Internet%' and Version like '10.0' THEN InvSoftware.client_id ELSE NULL END)) as CountInstallation
FROM InvSoftware
INNER JOIN Clients
    ON Clients.client_id = InvSoftware.client_id
GROUP BY substring (name, 3,3)

Deine Abfrage mit dem Sub-Select hätte auch funktioniert, wenn du in der Where-Klausel noch die Client_ID berücksichtigt hättest.

Viele Grüße,
Tommi
 
Werbung:
Hi,

hab mir mein Ergebnis noch einmal angesehen und das war natürlich ein klein wenig zu schnell geschossen.
Aber wenn man das ein klein wenig umstellt, funktioniert diese Vorgehensweise:

Code: 
SELECT
SUBSTRING (Clients.name, 3,3) as Location,
COUNT(Distinct Clients.client_id) as CountClients,
COUNT(DISTINCT (CASE WHEN ProductName like 'Internet%' and Version like '10.0' THEN InvSoftware.client_id ELSE NULL END)) as CountInstallation
FROM Clients
LEFT OUTER JOIN InvSoftware
    ON Clients.client_id = InvSoftware.client_id
GROUP BY substring (Clients.name, 3,3)

Viele Grüße,
Tommi
 
Zurück
Oben