Skip to main content
Fixed having function problem.
Source Link

When you have an aggregate function, you need a GROUP BY statement. In your case, it would be

SELECT 
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM, 
    SUM(UITLENINGEN.BOETE)
FROM 
    LEERLINGEN 
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
WHERE 
    (((UITLENINGEN.BOETE)>25))
GROUP BY
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM;

The GROUP BY clause comes after a WHERE clause but before an ORDER BY (or HAVING) clause on a SELECT statement. In this case, you want to get the sum of "Boete" for each combination of your other columns, hence the need to group by all of those other rows.

In short, if you have an aggregate function (like SUM, AVG, MIN, or MAX), you'll need to tell the database engine what to do with those non-aggregated columns. That's a simplistic way of explaining what GROUP BY does for you there.

Incidentally, if you want to find out cases where the sum of all Uitleningen.Boete is greater than 25 (rather than only including the records in which Uitleningen.Boete is greater than to 25), you actually have to use the HAVING clause. Then it would look like this:

SELECT 
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM, 
    SUM(UITLENINGEN.BOETE)
FROM 
    LEERLINGEN 
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM
HAVING
    ((SUM(UITLENINGEN.BOETE)>25));>25;

Think of HAVING as a WHERE for your groups. You filter rows using WHERE, and you filter groups using HAVING.

When you have an aggregate function, you need a GROUP BY statement. In your case, it would be

SELECT 
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM, 
    SUM(UITLENINGEN.BOETE)
FROM 
    LEERLINGEN 
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
WHERE 
    (((UITLENINGEN.BOETE)>25))
GROUP BY
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM;

The GROUP BY clause comes after a WHERE clause but before an ORDER BY (or HAVING) clause on a SELECT statement. In this case, you want to get the sum of "Boete" for each combination of your other columns, hence the need to group by all of those other rows.

In short, if you have an aggregate function (like SUM, AVG, MIN, or MAX), you'll need to tell the database engine what to do with those non-aggregated columns. That's a simplistic way of explaining what GROUP BY does for you there.

Incidentally, if you want to find out cases where the sum of all Uitleningen.Boete is greater than 25 (rather than only including the records in which Uitleningen.Boete is greater than to 25), you actually have to use the HAVING clause. Then it would look like this:

SELECT 
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM, 
    SUM(UITLENINGEN.BOETE)
FROM 
    LEERLINGEN 
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM
HAVING
    (((UITLENINGEN.BOETE)>25));

Think of HAVING as a WHERE for your groups. You filter rows using WHERE, and you filter groups using HAVING.

When you have an aggregate function, you need a GROUP BY statement. In your case, it would be

SELECT 
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM, 
    SUM(UITLENINGEN.BOETE)
FROM 
    LEERLINGEN 
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
WHERE 
    (((UITLENINGEN.BOETE)>25))
GROUP BY
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM;

The GROUP BY clause comes after a WHERE clause but before an ORDER BY (or HAVING) clause on a SELECT statement. In this case, you want to get the sum of "Boete" for each combination of your other columns, hence the need to group by all of those other rows.

In short, if you have an aggregate function (like SUM, AVG, MIN, or MAX), you'll need to tell the database engine what to do with those non-aggregated columns. That's a simplistic way of explaining what GROUP BY does for you there.

Incidentally, if you want to find out cases where the sum of all Uitleningen.Boete is greater than 25 (rather than only including the records in which Uitleningen.Boete is greater than to 25), you actually have to use the HAVING clause. Then it would look like this:

SELECT 
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM, 
    SUM(UITLENINGEN.BOETE)
FROM 
    LEERLINGEN 
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM
HAVING
    SUM(UITLENINGEN.BOETE)>25;

Think of HAVING as a WHERE for your groups. You filter rows using WHERE, and you filter groups using HAVING.

Source Link

When you have an aggregate function, you need a GROUP BY statement. In your case, it would be

SELECT 
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM, 
    SUM(UITLENINGEN.BOETE)
FROM 
    LEERLINGEN 
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
WHERE 
    (((UITLENINGEN.BOETE)>25))
GROUP BY
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM;

The GROUP BY clause comes after a WHERE clause but before an ORDER BY (or HAVING) clause on a SELECT statement. In this case, you want to get the sum of "Boete" for each combination of your other columns, hence the need to group by all of those other rows.

In short, if you have an aggregate function (like SUM, AVG, MIN, or MAX), you'll need to tell the database engine what to do with those non-aggregated columns. That's a simplistic way of explaining what GROUP BY does for you there.

Incidentally, if you want to find out cases where the sum of all Uitleningen.Boete is greater than 25 (rather than only including the records in which Uitleningen.Boete is greater than to 25), you actually have to use the HAVING clause. Then it would look like this:

SELECT 
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM, 
    SUM(UITLENINGEN.BOETE)
FROM 
    LEERLINGEN 
    INNER JOIN UITLENINGEN ON LEERLINGEN.LLNR = UITLENINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR, 
    UITLENINGEN.LLNR, 
    LEERLINGEN.VOORNAAM, 
    LEERLINGEN.TUSSENVOEGSEL, 
    LEERLINGEN.ACHTERNAAM
HAVING
    (((UITLENINGEN.BOETE)>25));

Think of HAVING as a WHERE for your groups. You filter rows using WHERE, and you filter groups using HAVING.