SQL aggregation using join

Question

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

What errors are you getting? – Jake Bathman Oct 09 '15 at 14:03 — Jake Bathman, Oct 09 '15 at 14:03
`LEFT JOIN` should be placed right after `FROM`. – Giorgos Betsos Oct 09 '15 at 14:05 — Giorgos Betsos, Oct 09 '15 at 14:05

score 2 · Answer 1 · answered Oct 09 '15 at 14:05

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

score 0 · Answer 2 · answered Oct 09 '15 at 14:06

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).

SQL aggregation using join

2 Answers2