Return most used text based on conditions

1

I have two tables SQLTableBlokke and Skaalkaarte. Both have tow common fields blokno and plaasno.
I want to return the most used text in a column (wynklas) from Skaalkaarte where blokno and plaasno are the same in both tables and where the jaar column in Skaalkaarte is equal to year(gettext()) -4
This returned value should be placed in klas_min4 in SQLTableBlokke.

I have tried the following sql command

 select s.blokno,s.jaar,s.wynklas, max(s.klascount) as getal
 from (
  SELECT p.blokno, t.wynklas,t.jaar, COUNT(*) AS KlasCount
  FROM SQLTableBlokke AS p
  JOIN Skaalkaarte AS t
   ON p.blokno = t.blokno and 
    t.jaar = year(getdate()) -4
 GROUP BY  p.blokno,t.jaar,t.wynklas) as s


  JOIN (SELECT s.blokno, MAX(s.RatingCount) AS MaxRatingCount
      FROM (SELECT p.blokno, t.wynklas, COUNT(*) AS RatingCount
              FROM SQLTableBlokke AS p
              JOIN Skaalkaarte AS t
              ON p.klas_min4 = t.wynklas and
			   p.blokno = t.blokno
             GROUP BY p.blokno, t.wynklas
           ) AS s
     GROUP BY s.blokno
   ) AS m
 ON s.blokno = m.blokno

I get the following error Column 's.blokno' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

This error is on line 1 of my code.

Any help or links to a tutorial (for beginners) would be much appreciated. Hoping that I am clear enough.

Regards

2

Without considering opmization and only looking at getting your code to work, it looks like you are missing a "group by" on the outer select - so if you add this to the end of your query, your current error should be solved:

 group by s.blokno,s.jaar,s.wynklas