SQL Query Aggregate May Not Appear in Where Clause

SQL Query aggregate may not appear in WHERE clause

Use the HAVING as hinted by the error message, which requires a GROUP BY:

SELECT
    SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied) as PastDueAmount, 
    M1.BillingID, M2.Name, 
    M2.DelinquentDaysThreshold, M2.DelinquentAmountThreshold,
    DATEDIFF(d, MIN(BillingDate),GETDATE()) as DaysLate
FROM
    Invoices M1
    LEFT JOIN
    ClientAccounts M2 ON M1.BillingID = M2.ID
WHERE
    InvoiceTotal <> AmountApplied
    AND
    M2.DelinquentDaysThreshold > DATEDIFF(d, MIN(BillingDate),GETDATE())
GROUP BY
    M1.BillingID, M2.Name, 
    M2.DelinquentDaysThreshold, M2.DelinquentAmountThreshold,
    DATEDIFF(d, MIN(BillingDate),GETDATE())
HAVING
    (SUM(M1.InvoiceTotal)-SUM(M1.AmountApplied)) > M2.DelinquentAmountThreshold

SQL - An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause

I think the query you want to write is something like:

select p.prod_cat, t.avg_total_amount
from (
    select prod_cat_code, avg(total_amount) as avg_total_amount
    from transactions
    group by prod_cat_code
    having avg(total_amount) > (select avg(total_amount) from transactions)
) t
inner join prod_cat_info p on p.prod_cat_code = t.prod_cat_code

The subquery aggregates transactions by category, compute the average, and compares it against the overall transaction average. Then we just bring the categories table with a join.

Aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause

The message means that aggregate functions (count) can only be used for filtering in a having clause

select count(i.item_id), warehouse_id from items_in_warehouse i
where date_from >= '2021-01-01' and date_from <= '2021-03-31'
group by warehouse_id
having count(warehouse_id) >= 5 and count(warehouse_id) <= 10;

unless it is in a subquery in a having clause

select count(i.item_id), warehouse_id from items_in_warehouse i
where date_from >= '2021-01-01' and date_from <= '2021-03-31'
group by warehouse_id
having (select count(1) from limits where limits.lower < count(i.item_id)) > 5

something like that

SQL Query aggregate may not appear in WHERE clause (AVG)

Use a subquery instead:

select p.ProductName, p.UnitPrice
from [DESKTOP-509LB9L\MSSQLSERVER01].northwind.dbo.products p
where p.UnitPrice > (select avg(p2.UnitPrice)
                     from [DESKTOP-509LB9L\MSSQLSERVER01].northwind.dbo.products p2
                    );

An aggregate may not appear in the WHERE clause( SQL Server Error )

The correct syntax would be...

select  (A.StudentId),max(A.StudentFirstName),
max(A.StudentLastName),max(A.StudentAddress),
'Batch ' + max(C.BatchName),CAST(MAX(CAST(A.StudentStatus as INT)) AS BIT),
max(B.StudentBatchId) 
from tblStudentDetails A  
inner join tblStudentBatchDetails B on A.StudentId=B.studentid 
inner join tblBatch C on C.BatchId=B.batchid 
group by A.StudentId
having max(A.StudentFirstName) like 'A%'

How to fix aggregate in where clause?

A full outer join is not needed for this query. For your logic, a left join would seem to be correct:

Select p.projectName, sum(a.costtodate) AS sumActivityCosts,
       p.fundedbudget
From Project p left join
     Activity a 
     on p.projectid = a.projectid  
where a.activityId is null and p.projectid is not null 
Group By p.projectName, p.fundedbudget
having sum(a.costtodate) > p.fundedbudget;

However, this doesn't make sense to me. If a.activityId is null, then the most likely reason would be no matches. So, I'm pretty sure you just want an inner join with no where clause:

Select p.projectName, sum(a.costtodate) AS sumActivityCosts,
       p.fundedbudget
From Project p inner join
     Activity a 
     on p.projectid = a.projectid
Group By p.projectName, p.fundedbudget
having sum(a.costtodate) > p.fundedbudget;

MySQL HELP aggregate may not appear in WHERE clause

Use a table alias in the subquery:

WHERE m.MemberID = 4 AND
      r.DateTimeEnd = (SELECT MAX(r2.DateTimeEnd) FROM Ride r2)

The expression r.DateTimeEnd in your query refers to the r in the outer query -- hence the error.

If you are only looking for the latest row, though, I would more likely write:

SELECT m.*, b.BikeStopID, r.DateTimeStart, r.DateTimeEnd
FROM Member m INNER JOIN
     Ride r
     ON m.MemberID = r.MemberID INNER JOIN
     Bike b
     ON r.BikeID = b.BikeID
WHERE m.MemberID = 4 
ORDER BY r.DateTimeEnd DESC
LIMIT 1;

Your version might return no rows if the most recent ride is for a different member.

An aggregate may not appear in the WHERE clause (MSSM studio)

You could use HAVING to filter rows after aggregation:

declare Users cursor for 
Select "User".IDUser, Sum(Price) from "User"
join Purchase as pu on "User".IDUser = pu.IDUser  
join PurchaseProduct as pp on pu.IDPurchase = pp.IDPurchase
join Product as pr on pp.IDProduct = pr.IDProduct
join inserted on pr.IDProduct = inserted.IDProduct
where pr.ProductType = (select ProductType from Product
              join Advertisement on Product.IDProduct = Advertisement.IDProduct
              join inserted on Advertisement.IDProduct = inserted.IDProduct
              where Advertisement.IDAdvertisement = inserted.IDAdvertisement)
GROUP BY "User".IDUser
HAVING Sum(Price) > 50;