SQL: count op twee kolommen

[Verwijderd]

Beste,

Ik probeer een COUNT op 2 kolommen te maken, om zo het aantal verzonden berichten per naam te achterhalen. Helaas wil dit niet echt lukken.

De tabellen:

tabel Bericht --------------- id PK van (=lidID) tekst

tabel Lid ------------------- id PK naam (=NIET uniek) status

Het gewenste Resultaat:
Naam (mag maximaal 1 voorkomen) | Aantal leden met deze naam | Aantal verzonden berichten


Queries die ik geprobeerd heb:

SELECT COUNT(lid.id) AS aantalLeden, COUNT(bericht.id) AS aantalBerichten, lid.naam
FROM lid, bericht
WHERE bericht.van = lid.id AND lid.status = 'actief'
GROUP BY lid.naam
ORDER BY lid.naam ASC

Bovenstaande query gaf als resultaat enkel het aantal verzonden berichten.


SELECT COUNT(berichtId) AS aantalBerichten, naam, aantalBerichten
FROM (
SELECT COUNT(lid.id) AS aantalLeden, lid.naam AS naam, bericht.id AS berichtId
FROM lid, bericht
WHERE lid.status = 'actief'
GROUP BY lid.naam
) AS virtualTable
GROUP BY naam

Bovenstaande query liet mijn database crashen (circa 500k bericht records).

Wie kan mij opweg helpen?
Alvast bedankt!

[Reinier]

Als je in plaats van een cartesisch product of een subquery gewoon een inner join maakt moet het wel lukken

[KopjeThee]

Misschien kan het een beetje opdelen (de syntax zal misschien niet precies kloppen):

code:

SELECT naam, aantalLedenMetNaam, aantalBerichten
FROM
  (SELECT naam, COUNT(*) AS aantalLedenMetNaam
   FROM Lid
   GROUP BY naam) 
   AS A
INNER JOIN
  (SELECT naam, COUNT(*) AS aantalBerichten
   FROM Lid INNER JOIN Bericht
   ON Lid.id = Bericht.van
   GROUP BY naam) 
   AS B
ON A.naam = B.naam

[ikke007]

SELECT l.naam,count(l.id),count(b.id)
FROM lid l
INNER JOIN bericht b
ON l.id = b.van
GROUP BY l.naam

zo even uit het bolleke. Wanneer jij per persoon het aantal berichten wilt hebben zal je de query weer een beetje moeten aanpassen.

[sopsop]

ikke007 schreef op donderdag 20 september 2007 @ 09:55:
SELECT l.naam,count(l.id),count(b.id)
FROM lid l
INNER JOIN bericht b
ON l.id = b.van
GROUP BY l.naam

zo even uit het bolleke. Wanneer jij per persoon het aantal berichten wilt hebben zal je de query weer een beetje moeten aanpassen.

Dit is nagenoeg dezelfde query als de eerste die TS post.

Wellicht is het hier mooier om de join niet op bericht te doen, maar op een subquery van bericht, die groupeert op lid id:

SQL:

SELECT  l.naam,count(l.id),Sum(b.aantal)
FROM Lid l
INNER JOIN (SELECT b.van, Count(*) AS aantal FROM Bericht GROUP BY van) b
ON b.van = l.id
GROUP BY l.naam