# Verständnisproblem bei SQL Abfrage



## Kirby.exe (17. Jan 2021)

Also ich habe die folgende Aufgabe:



Soweit habe ich verstanden, dass wir mittels den Tabellen Aufnahme, CD und Stueck zählen sollen, wie viele Stuecke auf einer CD sind und dann sollen wir diese Anzahl durch den Wert anz_cds (von der Tabelle CD) dividieren und in eine Tabelle schreiben. Aus dieser Tabelle werden dann die obigen Werte ermittelt. Ich verstehe aber nicht ganz wie ich diese Unteranfrage aufbauen soll... So werden die Tabellen gelinked:


```
Select s1.snr,
        s1.knr,
        s1.titel,
        s1.tonart,
        s1.opus,
        c1.cdnr,
        c1.name,
        c1.hersteller,
        c1.anz_cds,
        c1.gesamtspielzeit
From Stueck s1,
    Aufnahme a1,
    CD c1
Where a1.cdnr = c1.cdnr
        and s1.snr = a1.snr
```


----------



## mihe7 (17. Jan 2021)

Kirby.exe hat gesagt.:


> Ich verstehe aber nicht ganz wie ich diese Unteranfrage aufbauen soll...


Was genau verstehst Du daran nicht? Du brauchst die Anzahl der Stücke gruppiert nach cd_nr.


----------



## Kirby.exe (17. Jan 2021)

mihe7 hat gesagt.:


> Was genau verstehst Du daran nicht? Du brauchst die Anzahl der Stücke gruppiert nach cd_nr.


Danke das hilft schonmal sehr  Aber wie teile ich die Counts für jede CD dann? Also kann ich einfach (count(s1.snr)/c1.anz_cds) machen und er zählt dann erst alle und dividiert dann?


Also so:

```
With res as (Select c1.cdnr,
                    (count(s1.snr)/c1.anz_cds)
            From Stueck s1,
                    Aufnahme a1,
                    CD c1
            Where a1.cdnr = c1.cdnr
                    and s1.snr = a1.snr
            Group By c1.cdnr)
```


----------



## Kirby.exe (17. Jan 2021)

Habe es jetzt einfach so gemacht:

```
With res as (Select c1.cdnr,
                    count(s1.snr) as anz
            From Stueck s1,
                    Aufnahme a1,
                    CD c1
            Where a1.cdnr = c1.cdnr
                    and s1.snr = a1.snr
            Group By c1.cdnr)
Select round(min(res.anz/c2.anz_cds),1),
        round(max(res.anz/c2.anz_cds),1),
        round(avg(res.anz/c2.anz_cds),1)
From CD c2, res
Where res.cdnr = c2.cdnr
```

Diese Anfrage liefert das richtige Ergebnis


----------



## mihe7 (17. Jan 2021)

Ja, oder den Subselect direkt in der FROM-Clause angeben. Die Division könntest Du gleich in res durchführen. Ist es eigentlich vorgegeben/angedacht, ohne INNER JOIN zu arbeiten?


----------



## Kirby.exe (17. Jan 2021)

mihe7 hat gesagt.:


> Ja, oder den Subselect direkt in der FROM-Clause angeben. Die Division könntest Du gleich in res durchführen. Ist es eigentlich vorgegeben/angedacht, ohne INNER JOIN zu arbeiten?


Naja not really, aber wird hier nicht intern sowieso ein Join durchgeführt?


----------



## mihe7 (17. Jan 2021)

Das wird der Query-Optimizer richten, aber im Prinzip sagst Du: hey, bilde erstmal das Kreuzprodukt und filter mir die raus, für die gilt: ...


----------



## Kirby.exe (17. Jan 2021)

Yes you're right xD Ich hatte noch ein Problem zu einer ähnlichen Aufgabe....Also die Aufgabenstellung ist die folgende:


Ich habe ein "fertiges" Query, welches mir auch teile der erwarteten Aufgaben liefert. Jedoch jetzt zu meinem Problem...Die Filter Anfrage, welche sich die Anzahl der gesamten Stücken mit einer Tonart verschieden von Null holt gibt mir die korrekten Personen und Anzahlen her. Nun habe ich noch die anderen Tabellen mit eingebunden für Dur und Moll und nun fehlen plötzlich einträge...Ich sehe nicht ganz warum das passiert...Hier ist der Code:


```
With res as (Select k1.knr,
                    count(s1.tonart) as anz_tonart
            From Komponist k1,
                    Stueck s1
            Where k1.knr = s1.knr
            Group By k1.knr),
    dur as (Select k3.knr,
                count(s3.tonart) as anz_tonart
            From Komponist k3,
                Stueck s3
            Where k3.knr = s3.knr
                    and s3.tonart Like '%-dur'
            Group By k3.knr),
    moll as (Select k4.knr,
                    count(s4.tonart) as anz_tonart
             From Komponist k4,
                    Stueck s4
            Where k4.knr = s4.knr
                    and s4.tonart Like '%-moll'
            Group By k4.knr)
Select k2.name,
        k2.vorname,
        res.anz_tonart as anzahl_tonarten,
        round((dur.anz_tonart*100.0)/res.anz_tonart) as prozent_dur,
        round((moll.anz_tonart*100.0)/res.anz_tonart) as prozent_moll
From Komponist k2,
    res,
    dur,
    moll
Where k2.knr = res.knr
        and k2.knr = dur.knr
        and k2.knr = moll.knr
        and res.anz_tonart >= 3
Group By k2.name, k2.vorname, res.anz_tonart, dur.anz_tonart, moll.anz_tonart
```


----------



## mihe7 (17. Jan 2021)

Kirby.exe hat gesagt.:


> Ich sehe nicht ganz warum das passiert...


Ich auch nicht  Lass mal Zeile 34 weg. 

Die Behandlung von res kannst Du mit HAVING vereinfachen:

```
SELECT
  k.knr,
  count(s.tonart) as stuecke_ges
FROM Komponist k
  INNER JOIN Stueck s ON k.knr = s.knr
GROUP BY k.knr
HAVING count(s.tonart) >= 3
```
Damit liefert der Subselect schon mal von Haus aus nur noch die Komponisten mit wenigstens drei Stücken, für die die Tonart bekannt ist und die Gesamtzahl dieser Stücke (count(s.tonart) liefert nicht die Zahl verschiedener Tonarten sondern die Zahl der Datensätze, deren Tonart nicht NULL ist und somit die Anzahl der gesuchten Stücke).

Kannst Du mal ein SQL-Dump posten (CREATE TABLE, INSERTs)? Was für eine DB verwendest Du?


----------



## Kirby.exe (17. Jan 2021)

Puhh also verwendet wird POSTGreSQL xD Das ist eine Uni Interne DB aber ich kann man Screenshots von den Tables machen

Hier sind die beiden Tabellen:#


----------



## mihe7 (17. Jan 2021)

So viel zum Thema OpenData  

OK, dann andersrum:



Kirby.exe hat gesagt.:


> Die Filter Anfrage, welche sich die Anzahl der gesamten Stücken mit einer Tonart verschieden von Null holt gibt mir die korrekten Personen und Anzahlen her. Nun habe ich noch die anderen Tabellen mit eingebunden für Dur und Moll und nun fehlen plötzlich einträge..



Fehlen Datensätze oder Zahlen?


----------



## Kirby.exe (17. Jan 2021)

mihe7 hat gesagt.:


> So viel zum Thema OpenData
> 
> OK, dann andersrum:
> 
> ...


Datensätze XD Wenn ich die Anfrage ausführt um nur die Anzahl der Tonarten zu bekommen (also egal ob dur oder moll) dann erhalte ich 9 Datensätze. Wenn ich aber alles zusammen verwende bekomme ich nur 7 Datensätze...xD Was mich verwirrt ist, dass ich doch gar nichts einschränke oder? Ich linke nur die res tabelle mit der dur und moll tabelle zusammen oder nicht ? xD


----------



## mihe7 (17. Jan 2021)

Kirby.exe hat gesagt.:


> Wenn ich die Anfrage ausführt um nur die Anzahl der Tonarten zu bekommen (also egal ob dur oder moll) dann erhalte ich 9 Datensätze.


Nochmal: das ist die Anzahl der Stücke, nicht der Tonarten. Sind da zufälligerweise Sätze dabei, für die anz_tonart < 3 gilt?


----------



## Kirby.exe (17. Jan 2021)

Nope alle haben mindestens 3 Stück:


----------



## mihe7 (17. Jan 2021)

Ich bezog mich auf die nicht verknüpfte Abfrage, wo Du 9 Datensätze bekommst


----------



## Kirby.exe (17. Jan 2021)

Ahhh ich glaube ich habe eine vermutung....Bei den beiden welchen Fehlen ist die Anzahl von Moll Tonarten = 0...XD Kann es sein, dass diese dann gar nicht im Query als in der Tabelle Moll gelistet werden?


----------



## Kirby.exe (17. Jan 2021)

Hier ist die nicht verknüpfte Anfrage:


----------



## mihe7 (17. Jan 2021)

Hä, kann es sein, dass die moll-Subquery nur 7 Sätze liefert?!?


----------



## mihe7 (17. Jan 2021)

Ah, das sind diese kleinen Fallstricke. Das Problem ist, dass wir Komponisten mit Stuecke joinen und dann nach der Tonart einschränken. Wenn ein Komponist kein Stück dieser Tonart hat, dann ist der Join leer und damit liefert moll auch keinen Satz für diesen Komponisten... Analog für dur.


----------



## Kirby.exe (17. Jan 2021)

mihe7 hat gesagt.:


> Ah, das sind diese kleinen Fallstricke. Das Problem ist, dass wir Komponisten mit Stuecke joinen und dann nach der Tonart einschränken. Wenn ein Komponist kein Stück dieser Tonart hat, dann ist der Join leer und damit liefert moll auch keinen Satz für diesen Komponisten... Analog für dur.


Ja gut xD Wie kann ich da jetzt nen Default Case machen? Also wenn Zeile nicht existiert dann einfach auf 0 setzen xD oder so?


----------



## mihe7 (17. Jan 2021)

Alle Komponisten und die dazu passenden Sätze aus moll (bzw. dur). Wonach schreit das?


----------



## Kirby.exe (17. Jan 2021)

mihe7 hat gesagt.:


> Alle Komponisten und die dazu passenden Sätze aus moll (bzw. dur). Wonach schreit das?


Ehm sorry ich stehe gerade auf dem Schlauch xD Maybe ein Join ? xD


----------



## mihe7 (17. Jan 2021)

LEFT JOIN.


----------



## Kirby.exe (17. Jan 2021)

Soo jetzt habe ich schonmal die einträge, aber wie mache ich jetzt aus dem NULL eine 0 xD


----------



## mihe7 (17. Jan 2021)

Kirby.exe hat gesagt.:


> aber wie mache ich jetzt aus dem NULL eine 0


COALESCE


----------



## Kirby.exe (17. Jan 2021)

Alright xD Ich habe jetzt sowas wie Cases gefunden, aber das kommt vom JOIN, das knr ambigous ist xD Keine Ahnung wie man das umgehen könnte 


```
Select ks.knr,
       sum(case when ks.tonart is not null then 1 else 0 end) as gesamt,
       sum(case when ks.tonart Like '%-dur' then 1 else 0 end) as dur,
       sum(case when ks.tonart Like '%-moll' then 1 else 0 end) as moll
From (Komponist
      JOIN Stueck ON Komponist.knr = Stueck.knr) as ks
GROUP BY ks.knr
```

Edit:

Ich sollte mehr Kaffe trinken xD
So funktionierts:

```
Select Komponist.knr,
       sum(case when Stueck.tonart is not null then 1 else 0 end) as gesamt,
       sum(case when Stueck.tonart Like '%-dur' then 1 else 0 end) as dur,
       sum(case when Stueck.tonart Like '%-moll' then 1 else 0 end) as moll
From Komponist
        JOIN Stueck ON Komponist.knr = Stueck.knr
GROUP BY Komponist.knr
```


----------



## Kirby.exe (17. Jan 2021)

Update:

Ich habe es gelöst xD Meine gute heute war einfach nicht mein Tag xD


```
With gesamt as (Select Komponist.knr,
                sum(case when Stueck.tonart is not null then 1 else 0 end) as stuecke_ges,
                sum(case when Stueck.tonart Like '%-dur' then 1 else 0 end) as anz_tonart_dur,
                sum(case when Stueck.tonart Like '%-moll' then 1 else 0 end) as anz_tonart_moll
            From Komponist JOIN Stueck ON Komponist.knr = Stueck.knr
            GROUP BY Komponist.knr)
Select name,
        vorname,
        stuecke_ges,
        round((anz_tonart_dur*100.0)/stuecke_ges) as prozent_dur,
        round((anz_tonart_moll*100.0)/stuecke_ges) as prozent_moll
From Komponist JOIN gesamt on Komponist.knr = gesamt.knr
Where stuecke_ges >= 3
Group By name, vorname, stuecke_ges, anz_tonart_dur, anz_tonart_moll
```


----------



## mihe7 (17. Jan 2021)

Kirby.exe hat gesagt.:


> Ich habe jetzt sowas wie Cases gefunden


Geht auch. Du hättest aber einfach nur noch COALESCE() verwenden müssen


----------

