SQL Error: Misuse of Aggregate

SQL error: misuse of aggregate

When using an aggregate function (sum / count / ... ), you also have to make use of the GROUP BY clause.

Next to that, when you want to filter on the result of an aggregate , you cannot do that in the WHERE clause, but you have to do that in the HAVING clause.

SELECT p1.domain_id, p2.domain_id, COUNT(p1.domain_id) AS d1, COUNT(p2.domain_id) AS d2
    FROM PDB as p1, Interacting_PDBs as i1, PDB as p2, Interacting_PDBs as i2
    WHERE p1.id = i1.PDB_first_id
    AND p2.id = i2.PDB_second_id
    AND i1.id = i2.id
GROUP BY p1.domain_Id, p2.domain_Id
HAVING d1 > 100 AND d2 > 100
ORDER BY d1, d2;

Misuse of aggregate function avg()

Use a subquery to find the average over the entire table:

SELECT *
FROM recent_grads
WHERE ShareWomen > (SELECT AVG(ShareWomen) FROM recent_grads);

This is straightforward and is what I would recommend. For a more sophisticated version, you could use AVG() as an analytic function:

WITH cte AS (
    SELECT r.*, AVG(ShareWomen) OVER () AS AvgShareWomen
    FROM recent_grads r
)

SELECT *
FROM cte
WHERE ShareWomen > AvgShareWomen;

misuse of aggregate: COUNT()

Perhaps you are confusing WHERE with HAVING:

SELECT A.imie, A.nazwisko, A.numer, COUNT(*) AS ile
FROM abonent A JOIN
     rozmowa R
     ON A.id = R.id_abonent_to
GROUP BY A.id, A.imie, A.nazwisko, A.numer
HAVING COUNT(*) > 4;

Not all databases support column aliases in HAVING. If yours does, you can use HAVING ile > 4.

Note that a LEFT JOIN is unnecessary because you are requiring at least 5 rows that match. (I suppose there could be other interpretations of your query, but I'm guessing that the multiple rows being counted come from R.)

The COUNT(*) is then simplification. The column being used for the COUNT() is part of the ON. Your version of COUNT() is appropriate for a LEFT JOIN, but unnecessary for what you are doing.

SQLite Misuse of aggregate: SUM()

The field in an order by statement is evaluated after the select statement. This means it can only reference fields which are in the select statement already. Try:

SELECT email_to, count(*) as total_emails FROM emails WHERE email_box='SENT' group by email_to ORDER BY total_emails

In the above query, the order by statement is able to reference the count of appearances with the field "total_emails".

Strange misuse of aggregate: max() error in SQLite

In SQL, double quotes are used to refer to column/table names, and test_id indeed is a column name. Strings are delimited with single quotes:

SELECT max(id) AS test_id FROM test WHERE val = 'test_id'

SQLite allows double quotes for strings in order to be bug-compatible with MySQL, but when in doubt, it has to choose the standard interpretation.

Misuse of aggregate in SQLite

Basically, you can't reference an aliased column in a where clause. Take a look at this question to see how to workaround that.

Aditionally, you should add to the group by all non-aggregated columns present in the select statement, in this case, c.id and c.title.

It is difficult to rewrite that query for you because the logic is a bit difficult to understand (because of the wrong group by).

Edit:

After a second thought, you might just need to correct the group by.

select c.id, c.title, sum(some_expression) as ratio
from doesntmatter
where c.active = 1 or (c.active = 0 and c.deactivated in (1, 2, 3, 4, 5))
group by c.id, c.title
having ratio = 1.0

Or maybe:

select f.rid, sum(some_expression) as ratio
from doesntmatter
where c.active = 1 or (c.active = 0 and c.deactivated in (1, 2, 3, 4, 5))
group by f.rid
having ratio = 1.0

I can't help you with the previous part because I don't know the fields nor the data of your tables nor how to interpret them. But selecting fields not present in the group by is a bad idea. Anyway, remember you can always join back to the original tables and get the info you are looking for, not just the fields displayed in the select.

How to solve error Result: misuse of aggregate function json_group_array()?

You can't nest an aggregate functions like json_group_array() inside another.

Maybe you want to aggregate on the results of the query:

SELECT json_object('a', json_group_array(json_result)) json
FROM (
  SELECT json_object(
                      'b', rm.id, 
                      'c', '{}',
                      'd', l.code,
                      'details', 
                      json_group_array(
                          json_object('e', rmd.id, 'f', rmd.s_id, 'g', s.name)
                      )
                    ) json_result
  FROM ....
)

could not prepare statement (1 misuse of aggregate function COUNT())

...have 0 records in tblGetAsk

This means that there is no matching row in tblGetAsk for that userEmail of tblUser, so your condition should be where G.userEmail IS NULL:

select U.userEmail, U.firstname + ' ' + U.surName as fullname 
from tblUser as U 
inner join tblHitchhhiker as H on U.userEmail= H.userEmail
left outer join tblGetAsk as G on U.userEmail = G.userEmail
where G.userEmail IS NULL

You could also use EXISTS and NOT EXISTS instead of the joins:

select U.userEmail, U.firstname + ' ' + U.surName as fullname 
from tblUser as U 
where exists (select 1 from tblHitchhhiker as H where H.userEmail= U.userEmail)
  and not exists (select 1 from tblGetAsk as G where G.userEmail = U.userEmail)

Getting 'misuse of aggregate function MIN()' error when I try to delete rows from a table

As @forpas and @CL rightfully intuited in the comments, it was a trigger problem. So the error was stemming from a trigger in another table, Groups, which was referentially linked to the Users table via the foreign key GroupMember in the Groups table. The foreign key was set to cascade the deletion of records matching the deleted Users username hence activating the ON DELETE trigger in the Groups table. The DELETE trigger had the aggregate MIN() function in its WHERE clause--of which it is apparently illegal in SQLite to have aggregate functions in the WHERE clause--hence the error message. Here is the original trigger code (pardon the logical holes in the code as well):

UPDATE Group_Members
SET Role = 'Admin'
WHERE JoinDate = MIN(JoinDate) AND GroupName = OLD.GroupName

Here's how I finally fixed the situation:

UPDATE Group_Members
SET Role = 'Admin'
WHERE JoinDate = (SELECT MIN(JoinDate) FROM Group_Members WHERE GroupName = OLD.GroupName)

---

Related Topics