Calculating commission

I am trying to calculate com开发者_高级运维missions payable to clients at the beginning of every month (providing their commission is more then £25).

Here is the SQL:

SELECT  SUM(invoiceCommision) as totalSum
FROM    tbl_statement_items
WHERE   fk_rid = '1'
  AND   dt > DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
  AND   totalSum > 25;

However when I run this, mysql says: 1054 - Unknown column 'totalSum' in 'where clause'.

I then tried

SELECT  *
FROM    tbl_statement_items
WHERE   fk_rid = '1'
  AND   dt > DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 
  AND   SUM(invoiceCommision) > 25;

This gives me error: 1111 - Invalid use of group function.

I am stumped on this one, any help would be appreciated.

For your first attempt, it didn't work because you can't use the computed fields in your SELECT clause in your WHERE clause (WHERE is processed before the fields are computed).

For the second attempt, it didn't work because you can't use aggregate functions in the WHERE clause. Again, just stick to your first attempt but change WHERE to HAVING.

There is also a more general problem that your SUM() function will just get the total sum of all statement items, whereas you want it per client. You can use GROUP BY to get a list of sums per client, and a HAVING clause to only return those with a sum greater than 25.

Try this (change client_id to whatever it really is):

SELECT client_id, SUM(invoiceCommision) as totalSum
FROM tbl_statement_items
WHERE fk_rid = '1' AND dt > DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY client_id
HAVING totalSum > 25;

You need to tell MySQL how to group the data when using grouping functions such as SUM

I expect you require something like this:

SELECT *, SUM(invoiceCommision) as commisionSum
FROM tbl_statement_items 
WHERE fk_rid = '1' 
    AND dt > DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 
GROUP BY fk_rid 
HAVING commisionSum > 25;

You must use the HAVING function when you require GROUP based conditions such as SUM, MAX etc