Access: ‘Cannot group on fields selected with '*'.’

Question

I am trying to select rows based on various like criterias, and have written the following function:

SELECT *,
COUNT(YRKE) AS Antall_med_yrket
FROM [T8 kopi (til sletting) av T7 3]
WHERE  YRKE like "*læge*"
    OR YRKE like "*præst*"
    OR YRKE like "*Lærer*"
    OR YRKE like "*lærer*"
    OR YRKE like "*chef.*"
    OR YRKE like "*mester*"
    OR YRKE like "*Portner*"
    OR YRKE like "*portner*"
    ⋮
    OR YRKE like "*fuglefanger*"
    ⋮
    OR YRKE like "*Glasanfanger*"
GROUP BY YRKE
;

The ‘⋮’ denotes omitted lines. What I want to do, is show all rows where the denoted words occur, and show the count for hits on these words. Example output:

ID   NAVN           YRKE                      Antall_med_yrket
 1   Ole Olsen      Fuglefanger               3
 2   Per Olsen      Skogbruger (Fuglefanger)  3
 3   Olav Krogh     Fængselslæge              1
 4   Kristen Persen Gaardbruger (Fuglefanger) 3
 5   Per Kristensen Fængselsdirecteur         1

However, when I add the COUNT line, I get the error message as showed in the title. Removing the line gives the output of all rows as desired, but of course without the count. What am I doing wrong?

I am using MS Access 2007 on Oracle.

Answer 1

This is correct. The following should work:

SELECT YRKE, COUNT(YRKE) AS Antall_med_yrket
FROM [T8 kopi (til sletting) av T7 3]
WHERE  YRKE like "*læge*"
    OR YRKE like "*præst*"
    OR YRKE like "*Lærer*"
    OR YRKE like "*lærer*"
    OR YRKE like "*chef.*"
    OR YRKE like "*mester*"
    OR YRKE like "*Portner*"
    OR YRKE like "*portner*"
    ⋮
    OR YRKE like "*fuglefanger*"
    ⋮
    OR YRKE like "*Glasanfanger*"
GROUP BY YRKE;

In a SQL aggregation query, the columns in the SELECT generally need to be one of the following:

• Explicitly mentioned in the GROUP BY.
• An expression formed by columns explicitly mentioned in the GROUP BY.
• The arguments to aggregation functions, such as SUM() or MIN().

Note: there is one exception allowed in some databases (essentially, you can say GROUP BY id and use the other columns when id is unique). However, neither MS Access nor Oracle support that functionality.