CODEWITHSUNDEEP
mysqlsql
fifas
fifas

Reputation: 11

SQL query execution fails

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

Upvotes: 0

Views: 68

Answers (5)

Sergey
Sergey

Reputation: 5217

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

You can use a HAVING clause to test the value of an aggregate function. It doesn't even have to be the same aggregate used in the projection. You can evaluate different functions over different columns (and combine them with logical expressions) as long as it makes sense to apply the aggregate over the specified group. The same grouping will be used for all the aggregate expressions.

Upvotes: 0

Bharat Nebhnnani
Bharat Nebhnnani

Reputation: 1

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

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415790

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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.

Upvotes: 1

HypedBuddy
HypedBuddy

Reputation: 11

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).

Upvotes: 0

Related Questions