SQL query execution fails-CodePudding

I am trying to execute query and it doesnt work.

SELECT id, 
       idOperacije, 
       idRadniNalog, 
       sum(kolicina) as total
FROM `artikli_presetorijum_zapisi` 
WHERE idRadniNalog = 9 
AND idOperacije = 105 
AND total > 2000
GROUP BY idOperacije

Error I get is:

Unknown column 'total' in 'where clause' Even if I use sum(kolicina) in where statement I get same issue.

I need to sum(kolicina) for idRadniNalog and idOperacije and display only items where sum is higer than 2000

CodePudding user response：

You can't refer to an alias or an aggregate in the WHERE clause. Move the restriction on the total to the HAVING clause.

SELECT idOperacije, SUM(kolicina) AS total
FROM artikli_presetorijum_zapisi
WHERE idRadniNalog = 9 AND idOperacije = 105
GROUP BY idOperacije  
HAVING total > 2000;

Note that as we are aggregating by idOperacije only this column and aggregates of other columns belong in the SELECT clause.

CodePudding user response：

The where clause filters the rows used to execute the query. You're trying to get only the rows that have sum > 2000 so you need to use the HAVING clause after the GROUP BY one. In this way you filter the first rows you impact in your query, you group them in groups of 'idOperacije' and verify the condition you need on those groups (the sum is not a column of your db i think so you can't use it as a condition for where).

CodePudding user response：

SELECT idOperacije, idRadniNalog, sum(kolicina) as total
    FROM `artikli_presetorijum_zapisi` 
     group by idOperacije ,idRadniNalog
    HAVING SUM(kolicina)>2000

CodePudding user response：

Items in the WHERE clause are evaluated as each row is encountered, before the grouping is finished, and therefore you can't use items that need aggregate functions (like SUM()). Instead, put that condition in the HAVING clause:

SELECT id, idOperacije, idRadniNalog, sum(kolicina) as total
FROM `artikli_presetorijum_zapisi` 
WHERE idRadniNalog = 9 and idOperacije = 105 
GROUP BY idOperacije 
HAVING sum(kolicina) > 2000

It's also poor practice to include items in the SELECT clause that are not also either accounted for in the GROUP BY clause or aggregated with a function like SUM(). Specifically, the id, and idRadniNalog fields are non-deterministic here, and it might be possible to get different results each time you run the query. Most databases don't even allow this (MySql is kind of bad this way). You should include these in your GROUP BY clause even if the existing idOperacije field is enough to uniquely identify those values.

SELECT id, idOperacije, idRadniNalog, sum(kolicina) as total
FROM `artikli_presetorijum_zapisi` 
WHERE idRadniNalog = 9 and idOperacije = 105 
GROUP BY idOperacije, id, idRadniNalog
HAVING sum(kolicina) > 2000

CodePudding user response：

I am also getting the same problem when I use the alias name with any Column... Just try this code this time it will work.

SELECT id, 
       idOperacije, 
       idRadniNalog, 
       sum(kolicina)
FROM `artikli_presetorijum_zapisi` 
WHERE idRadniNalog = 9 
and idOperacije = 105 
and sum(kolicina) > 2000
group by idOperacije