Wert NULL nicht mitberechnen

[gnuso]

Hallo,

ich habe folgendes Problem. Ich soll eine Tabelle ausgeben, bei der die Anzahl der Filme im Store mit der ID = 1 ausgegeben werden. Die Filme sollen auf B enden. Zudem ist eine Filmkopie verfügbar, d.h. sie steht zur Ausleihe bereit, wenn sie bisher noch nie ausgeliehen wurde oder wenn es keine Ausleihe (rental) gibt, die noch nicht zurückgegeben wurde.

Ich habe folgenden Code:

select inventory.store_id store_id, film.title Titel_Film, count(film.film_id) Anz_ausleihbar
from film
join inventory on film.film_id = inventory.film_id
join (select rental.inventory_id, SUM(rental.return_date) from rental
WHERE rental.return_date IS NOT NULL
group by rental.inventory_id) rental2
on rental2.inventory_id = inventory.inventory_id
where film.title like '%B'
and inventory.store_id = 1
group by inventory.store_id, film.title
order by store_id asc, film.title desc

Dieser gibt mir 2 Filme aus die auf B enden und die Anzahl 2 und 3. Ich weiß aber das von einem Film nur 2 auf Lager sind, da einer ausgeliehen ist. Das habe ich dadurch gesehen, da in return_date ein NULL steht.

Könnt ihr mir auf die Sprünge helfen wo mein Fehler ist?

 

[akretschmer]

als schnellschuß: wenn Du weißt, daß bei return_date NULL dieser nicht mitgezählt werden darf, warum steht dann im Where rental.return_date IS NOT NULL ?

 

[gnuso]

ich dachte, da ich mit SUM(rental.return_date) die Summe berechne, ich glaube aber eher es müsste COUNT sein. Und da ich eben nur die zählen möchte, bei denen ein wert drinne steht -> IS NOT NULL

oder irre ich mich?

 

[akretschmer]

sorry, ich war nicht ganz bei der sache. Ja, deine letzte Antwort macht sinn. Ich kenne aber die Tabellen und Daten nun auch nicht wirklich.

 

[gnuso]

Kein Problem.

Also in meinen Tabellen habe ich die (interessanten) Spalten wie folgt:

Tabelle film
film_id (Primärschlüssel), title

Tabelle inventory
inventory_id (Primärschlüssel), film_id, store_id

Tabelle rental
rental_id(Primärschlüssel), inventory_id, return_date

Ich hoffe du kannst damit was anfangen.

Meine Überlegung ist eben diese, zuerst die Filme zählen, welche ein return_date haben und danach diese über inventory zu film zu joinen um den Titel und store_id rauszubekommen.

 

[drdimitri]

Ich formatier das Statement mal:

  SELECT inventory.store_id store_id,
         film.title Titel_Film,
         COUNT (film.film_id) Anz_ausleihbar
    FROM film
         JOIN inventory ON film.film_id = inventory.film_id
         JOIN (  SELECT rental.inventory_id, SUM (rental.return_date)
                   FROM rental
                  WHERE rental.return_date IS NOT NULL
               GROUP BY rental.inventory_id) rental2
            ON rental2.inventory_id = inventory.inventory_id
   WHERE film.title LIKE '%B' AND inventory.store_id = 1
GROUP BY inventory.store_id, film.title
ORDER BY store_id ASC, film.title DESC

Zum einen ist return_date ist definitiv kein Datentyp DATE, denn den kann man nicht summieren. Ein Datum sollte aber immer auch als DATE gespeichert werden.
Zum anderen ist return_date sicherlich nie NULL, weil es ansonsten den Datensatz ja nicht geben würde - oder gibt es dort Zeilen die nur aus dem PK bestehen?

Was ist den der Hintergrund der Abfrage? Herausfinden ob ein Film gerade ausgeliehen ist?

 

[akretschmer]

test=*# select * from film ;
 film_id | title  
---------+--------
  1 | film 1
  2 | film 2
  3 | film 3
(3 Zeilen)

test=*# select * from inventory ;
 inventory_id | film_id | store_id
--------------+---------+----------
  1 |  1 |  1
  2 |  1 |  1
  3 |  1 |  1
  4 |  2 |  1
  5 |  2 |  1
(5 Zeilen)

test=*# select * from rental ;
 rental_id | inventory_id | return_date
-----------+--------------+-------------
  1 |  2 | 2017-02-01
  2 |  2 |
(2 Zeilen)

inventory_id = 2 war mal ausgeliehen, und ist es jetzt.

test=*# select f.title, count(1) from inventory i left join film f on i.film_id=f.film_id left join rental r on r.inventory_id=i.inventory_id and r.return_date is null where r.rental_id is null group by title;
 title  | count
--------+-------
 film 2 |  2
 film 1 |  2
(2 Zeilen)

Checks auf die store_id etc. habe ich nicht weiter gemacht.

 

[gnuso]

Danke für eure Hilfe, ich habe jetzt folgenden Code, der auch funktioniert, doch leider bekomme ich jetzt den Fehler Error: ORA-00979: not a GROUP BY expression. Ich denke der Code ist nicht der schönste, dennoch funktioniert er :-D

Ich weiß, dass rental.return_date auch in die GROUP BY expression müsste, wenn ich das jedoch tue, bekomme ich wieder eine andere Ausgabe...

SELECT store_id, Titel_Film, COUNT(Anz_ausleihbar) Anz_ausleihbar
FROM (
SELECT rental.return_date Anz_ausleihbar, inventory.store_id store_id, film.title Titel_Film
FROM rental
JOIN inventory
ON rental.inventory_id = inventory.inventory_id
JOIN film
ON inventory.film_id = film.film_id
WHERE film.title like '%B'
AND inventory.store_id = 1
GROUP BY inventory.inventory_id, film.title)
WHERE Anz_ausleihbar IS NOT NULL
GROUP BY Titel_Film, store_id

 

[akretschmer]

geht es nun oder kommt eine Fehler? Und warum sollte ein feld, was nicht in der select-Liste ist, in das GROUP BY? Das innere GROUP BY funktioniert so? Warum ein full join zwischen inventory und rental?

*verwirrt*

 

[gnuso]

Nicht nur du bist verwirrt, also in SQLite Browser bekomme ich die Ausgabe, aber unter Oracle 11g bekomme ich den GROUP BY Fehler

rental.return_date steht in der 2. Select Liste also der in der Klammer. Und ich denke das innere GROUP BY funktioniert nicht, dort liegt der Fehler, aber ich weiß nicht wie ich dies "umgehen" kann??

 

[akretschmer]

mal anders gefragt? was ist an meiner Lösung unpassend?