Problem mit count(), GROUP BY und mehreren Tabellen

Hallöchen! Ich hätt’ da mal 'ne Frage :p

Ich möchte mir die Schauspieler & Schauspielerinnen meiner FilmDB nach Nation gezählt anzeigen lassen.
Dazu frage ich folgende drei Tabellen ab:

        nation                            actor                                 actress
+-----------+---------+      +-----------------+-----------+      +-------------------+-----------+
| nation_id | country |      |      name       | nation_id |      |       name        | nation_id |
+-----------+---------+      +-----------------+-----------+      +-------------------+-----------+
|         1 |   FRA   |      | Jason Statham   |         2 |      | Frances McDormand |         3 |
|         2 |   GBR   |      | Christopher Lee |         2 |      | Sean Young        |         3 |
|         3 |   USA   |      | Vincent Cassel  |         1 |      | Julie Delpy       |         1 |
|         4 |   DEU   |      | Robert De Niro  |         3 |      | Helen Mirren      |         2 |
|    ...    |   ...   |      |      ...        |    ...    |      |       ...         |    ...    |
+-----------+---------+      +-----------------+-----------+      +-------------------+-----------+

Wenn ich mit folgender Abfrage nur EINE Tabelle JOINe:

SELECT    n.country
          ,count(a.nation_id) AS count_actor
FROM      nation n
LEFT JOIN actor a USING (nation_id)
GROUP BY  n.country
ORDER BY  n.country;

erhalte ich das gewünschte Resultat:

+---------+-------------+
| country | count_actor |
+---------+-------------+
|   DEU   |           0 |
|   FRA   |           1 |
|   GBR   |           2 |
|   USA   |           1 |
+---------+-------------+

Wenn ich aber mit dieser Query die Damen als ZWEITE Tabelle dazuJOINe:

SELECT    n.country
          ,count(a.nation_id) AS count_actor
          ,count(b.nation_id) AS count_actress
FROM      nation n
LEFT JOIN actor   a USING (nation_id)
LEFT JOIN actress b USING (nation_id)
GROUP BY  n.country
ORDER BY  n.country;

stimmen die Zahlen nicht mehr. Zumindest in den Zeilen, in denen es actors UND actresses gibt… also z.B.:
Habe ich zwei italienische Schauspieler (aber keine ital. Schauspielerin) wird mir korrekt “count_actor” = 2, “count_actress” = 0 angezeigt.
Habe ich jetzt aber z.B. zwei Amerikaner UND eine Amerikanerin kommt nicht “count_actor” = 2 UND “count_actress” = 1 zurück,
sondern eine Zahl, die ich nicht nachvollziehen kann… z.B. “count_actor” = 7 UND “count_actress” = 7 (erstaunlicherweise die Selbe).

Was verpasse ich denn da? Bzw., welchen Denkfehler bau’ ich da ein? (Version: Postgres 9.3.6, OS: Fedora, Wissensstand: Hobbyist :slight_smile: )

LG, cornfed.

zähle mit sum(case when … then 1 else 0 end) bzw. ich weiß nicht, ob 9.3 das schon kann, via count(*) filter (where …), aber das geht glaube ich erst ab 9.4.

Danke für die wirklich schnelle Reaktion (nur knapp 10min)!
Aber leider,

1.) komm’ ich nicht drauf, wie die Bedingung in sum(CASE WHEN …) genau aussehen soll. Google hilft mir hier auch nicht wirklich…
sum(CASE WHEN (a.nation_id IS NOT NULL) THEN 1 ELSE 0 END) AS count_actor ? (gibt mir mit EINER Tabelle korrekte Ergebnisse)

2.) macht es nicht wirklich einen Unterschied, ob ich jetzt count() oder sum() benutze, das Problem beginnt IMMER mit dem
einbeziehen einer dritten Tabelle, egal ob sum() oder count() (siehe erste und zweite query im ersten post):

        alles gut                                dinge laufen schief
+---------+-------------+           +---------+-------------+---------------+
| country | count_actor |           | country | count_actor | count_actress |
+---------+-------------+           +---------+-------------+---------------+
|   DEU   |           0 |           |   DEU   |           0 |             0 |
|   FRA   |           1 |           |   FRA   |           5 |             5 |
|   GBR   |           2 |           |   GBR   |          21 |            21 |
|   USA   |           1 |           |   USA   |          13 |            13 |
+---------+-------------+           +---------+-------------+---------------+

3.) count(*) filter (where …) tatsächlich erst ab v9.4 (ja, habe schon länger vor, mein OS/Postgres zu aktualisieren :rolleyes:),
aber hier müsste ich auch erst einmal kräftig nachgoogeln (wie gesagt: Hobbyist), wobei ich aber eben denke, dass Problem besteht darin ->

4.) die Ergebnisse der beiden geJOINten Tabellen nicht zu vermischen. Was gäbe es da noch für Ansätze?

  • Window functions ? (OVER … PARTITION BY, ohne GROUP BY) hab’ ich auch probiert… mit nur EINER weiteren Tabelle stimmt alles… :angry:
  • Subqueries / WITH ?

Gruss, cornfed.

Ja, sorry. Der Ansatz über den Join ist schon mal für den Eimer:

test=*# select n.country, a_r.*, a_s.* from nation n right join actor a_r using(nation_id) right join actress a_s using (nation_id);
 country |    name     | nation_id |  name   | nation_id 
---------+-------------+-----------+---------+-----------
 fra     | vincent     |         1 | julie   |         1
 gbr     | jason       |         2 | helen   |         2
 gbr     | christopher |         2 | helen   |         2
 usa     | robert      |         3 | frances |         3
 usa     | robert      |         3 | sean    |         3
(5 Zeilen)

Du suchst eher sowas:

test=*# select n.*, (select count(1) from actor where actor.nation_id = n.nation_id), (select count(1) from actress where actress.nation_id=n.nation_id) from nation n order by 2;
 nation_id | country | count | count 
-----------+---------+-------+-------
         4 | deu     |     0 |     0
         1 | fra     |     1 |     1
         2 | gbr     |     2 |     1
         3 | usa     |     1 |     2
(4 Zeilen)

JA! So funktioniert das einwandfrei!

Sorry wofür :slight_smile: ? Ich muss mich eher nochmal für die schnelle und sehr konkrete Hilfe bei Dir bedanken. Jetzt verstehe ich auch,
warum das mit dem JOIN nicht geklappt hat (durch Dein 1. Beispiel)…

Danke nochmal, cornfed.

sorry, weil ich normalerweise es teste, bevor ich antworte :wink: