Doppelte Werte mit count nur einmal zählen

[cuchulainn]

Hallo,

ich bekomme eine Abfrage mit SQL nicht hin. Vielleicht sehe ich den Wald vor lauter Bäumen nicht mehr, aber ich schaffe es einfach nicht. Hier mal der Sachverhalt.

Es gibt eine Tabelle "Person" und eine Tabelle "Liegenschaften". Personen können mit beliebig vielen Liegenschaften handeln; zwischen den beiden Tabellen besteht eine n:m-Beziehung, die über die Tabelle "Vertrag" läuft. Nun kann eine Person mit einer Liegenschaft handeln und kann später irgendwann noch einmal mit derselben Liegenschaft handeln. Die Person kann eine Liegenschaft z.B. kaufen und später verkaufen und noch einmal später wieder kaufen. Über die Art des Geschäfts habe ich keine Informationen - ich weiß nur, dass die Person mit der Liegenschaft gehandelt hat. Anmerkung: Es geht um eine Datenbank für die mittelalterliche Geschichte, also fehlen diese Informationen meist.

Hier die Tabellen mit den relevanten Feldern:

Person = personId, name
Vertrag = vertragId, personID_fk, liegenschaftId_fk
Liegenschaft = liegenschaft_id, name

Jetzt brauche ich eine Abfrage, die mir anzeigt, mit wie vielen Liegenschaften eine Person gehandelt hat. Dabei darf eine Liegenschaft aber nur einmal gezählt werden. Wenn also Person A mit den Liegenschaften X, Y, Z und X gehandelt hat (also vier Einträge in Vertrag), darf als Ergebnis nur 3 angezeigt werden, da die Liegenschaft X nur einmal gezählt werden darf.

Bei dieser Anweisung werden allerdings Liegenschaften doppelt gezählt:

select p.personid, name, count(vertragId) from person p, vertrag v where p.personId = v.personId group by personId

Kann ich die Abfrage so modifizieren, dass dieselben Liegenschaften bei einer Person nur einmal gezählt werden?

Vielen Dank im Voraus,

Christopher

 

[Biergamasda]

Hi

Bin mir jetzt nicht sicher, ob das Schlüsselwort distinct bei einer Abfrage mit count funktioniert. Müsstest du mal ausprobieren.
Distinct bewirkt jedenfalls, dass doppelte Einträge nur einmal angezeigt werden.

Sollte es direkt nicht gehen, mach dir einfach eine Abfrage mit distinct, die dir die ganzen Einträge in einen View schreibt, So dass keine doppelten Einträge mehr vorkommen. Und anschließend lässt das count über deinen View laufen.

MfG

 

[Yaslaw]

In einem einfache SQL geht das nicht.

Ich hbae mal ein 2fach geschachteltes Query draus gemacht.

SELECT
	p.personid,
	p.name,
	v2.anzahl_handel
FROM
	person AS p,
	(	SELECT
			v1.personID_fk,
			COUNT(*) AS anzahl_handel
		FROM
			(	SELECT DISTINCT
					v.personID_fk, 
					v.liegenschaftId_fk
				FROM
					vertrag AS v
			) AS v1
		GROUP BY
			v1.personID_fk
	) AS v2	
WHERE 
	p.personId = v2.personID_fk

Und hier nun die Erklärung dazu:

Im innersten reduzieren wir die Verträge durch DISTINCT und weglassen der vertragId auf die Menge Vertragsmenge die dich interessiert.

				SELECT DISTINCT
					v.personID_fk, 
					v.liegenschaftId_fk
				FROM
					vertrag AS v

Eins darüber zähle ich die Verträge pro Person. Diesen Schritt könnte man auch auslassen, dann würden aber alle Personen mit jedem Vertrag verbunden und dann gruppiert über alle personen-Felder.

		SELECT
			v1.personID_fk,
			COUNT(*) AS anzahl_handel
		FROM
			( %v1%
			) AS v1
		GROUP BY
			v1.personID_fk

Am Schluss hänge ich noch die personen an um die Informationen zu vervollständigen

SELECT
	p.personid,
	p.name,
	v2.anzahl_handel
FROM
	person AS p,
	( %v2%
	) AS v2	
WHERE 
	p.personId = v2.personID_fk

 

[Matthias Reitinger]

Bin mir jetzt nicht sicher, ob das Schlüsselwort distinct bei einer Abfrage mit count funktioniert. Müsstest du mal ausprobieren.
Distinct bewirkt jedenfalls, dass doppelte Einträge nur einmal angezeigt werden.

Einige Datenbanksysteme unterstützten COUNT(DISTINCT …). Das würde dann so aussehen:

SELECT p.personId, p.name, COUNT(DISTINCT v.liegenschaftId_fk)
FROM person p
LEFT JOIN vertrag v ON (v.personId_fk = p.personId)
GROUP BY p.personId, p.name

Ansonsten halt wie yaslaw vorgeschlagen hat. Wenn man auch Personen mit 0 Liegenschaften haben will, verwendet man einen LEFT JOIN:

SELECT p.personId, p.name, COUNT(sub.liegenschaftId_fk)
FROM person p
LEFT JOIN (
  SELECT DISTINCT v.personId_fk, v.liegenschaftId_fk
  FROM vertrag v
) sub ON (p.personId = sub.personId_fk)
GROUP BY p.personId, p.name

Grüße,
Matthias

 

[Yaslaw]

Einige Datenbanksysteme unterstützten COUNT(DISTINCT …)

Thx. Man lernt nie aus