CODEWITHSUNDEEP
mysqlsqlaggregate-functions
Mihaly
Mihaly

Reputation: 301

SQL aggregation using join

I have 2 tables with Naam as primary key, the one table contains information on Naam (lumchartcentrumuser) and the other table contains information on presentations held by naam (lumchartecentrumonderwijs).

I want to use a bit more complex aggregation which counts the number of presentations grouped by Naam, using a where however i keep on getting errrors. Does anybody see what i am doing wrong : 

SELECT lumchartcentrumuser.Naam,
       COUNT(lumchartecentrumonderwijs.ID) AS Getal
FROM lumchartecentrumonderwijs
WHERE lumchartcentrumuser.Type <> 3
 AND lumchartecentrumonderwijs.Categorie <> "
LEFT JOIN lumchartcentrumuser ON
lumchartecentrumonderwijs.Naam=lumchartcentrumuser.Naam 
GROUP BY Naam

Upvotes: 2

Views: 69

Answers (2)

Jake Bathman
Jake Bathman

Reputation: 1278

You need to move your WHERE statements below the JOIN statements. Something like this

SELECT lumchartcentrumuser.Naam, COUNT(lumchartecentrumonderwijs.ID) AS Getal
FROM lumchartecentrumonderwijs 
LEFT JOIN lumchartcentrumuser 
ON lumchartecentrumonderwijs.Naam = lumchartcentrumuser.Naam
WHERE lumchartcentrumuser.Type <> 3 AND lumchartecentrumonderwijs.Categorie <> ""
GROUP BY Naam

Also make sure you close your quotation marks (fixed above).

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

The syntax is wrong. It should be like SELECT..FROM...JOIN...WHERE...GROUP BY

SELECT lumchartcentrumuser.Naam, COUNT(lumchartecentrumonderwijs.ID) AS Getal
FROM lumchartecentrumonderwijs 
LEFT JOIN lumchartcentrumuser 
ON lumchartecentrumonderwijs.Naam=lumchartcentrumuser.Naam 
WHERE lumchartcentrumuser.Type <> 3 AND 
      lumchartecentrumonderwijs.Categorie <> '' 
GROUP BY Naam

Upvotes: 2

Related Questions