SQL queries

kesho
10-18-2004, 11:14 AM
I am having a bit of bother trying to get the most out of my SQL queries. Could someone explain why the following is invalid in SQL server and MS Access (I am interested in using queries of this type for both).


SELECT MemInfo.[Name], MemInfo.Balance, MemInfo.MaxCash FROM MemInfo WHERE MemInfo.MaxCash > SUM(MemInfo.Balance) ;


To my mind this is a perfectly valid querie but it throws up an error in both SQL Server and Access. With SQL Server saying "-2147217900 - An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."
?|(

HardCode
10-18-2004, 12:16 PM
You cannot do an aggregate function (SUM, COUNT, etc) without using Group By. Conditions of an aggregate type must be in the HAVING clause. It should be something like this:


SELECT MemInfo.[Name],
MemInfo.Balance,
MemInfo.MaxCash
FROM MemInfo
GROUP BY MemInfo.[Name],
MemInfo.Balance,
MemInfo.MaxCash
HAVING MemInfo.MaxCash > SUM(MemInfo.Balance);

kesho
10-19-2004, 04:45 AM
Hi,

Thanks for the reply. I was quite hopefull when I looked at what you had said that i would be able to make the querie I am looking to make but unfortunately it is not logicaly the same. In my hypothetical querie I am looking for all the members who have MaxCash available greater than the sum of all the members balances. When I use group I get the sum of the fields I am testing on not the individual records.

kesho
10-19-2004, 05:18 AM
I was wrong in what I said above, but the SUM is being tested on all the items in the grouping.

So :-


SELECT MemInfo.[Name],
MemInfo.Balance,
MemInfo.MaxCash
FROM MemInfo
GROUP BY MemInfo.[Name],
MemInfo.Balance,
MemInfo.MaxCash
HAVING MemInfo.MaxCash > SUM(MemInfo.Balance);


is the same as saying :-


SELECT MemInfo.[Name],
MemInfo.Balance,
MemInfo.MaxCash
FROM MemInfo
GROUP BY MemInfo.[Name],
MemInfo.Balance,
MemInfo.MaxCash
HAVING MemInfo.MaxCash > MemInfo.Balance;

NEOLLE
10-19-2004, 07:18 AM
Hello Kesho,
Perhaps you could try this, :)

SELECT MemInfo.[Name],
MemInfo.Balance,
MemInfo.MaxCash
FROM MemInfo
WHERE MemInfo.MaxCash > (SELECT SUM(MemInfo.Balance) FROM MemInfo);

kesho
10-19-2004, 08:48 AM
Hello Kesho,
Perhaps you could try this, :)


Thanks that is spot on:

It is one ugly looking querie I have now but works great :)

Here it is


SELECT
(SELECT SUM([MemInfo].[Balance]) FROM MemInfo) AS [Total Of Balance],
MemInfo.MaxCash,
MemInfo.Balance,
Meminfo.[Name]
FROM MemInfo
WHERE
(SUM([MemInfo].[MaxCash]) <
(SELECT SUM([MemInfo].[Balance]) FROM MemberBalances)
)

kko24
10-19-2004, 08:50 AM
The sum, count, etc are not permited but in having clause
the error is the "..sum.." in the "were" clause

kesho
10-19-2004, 11:52 AM
The sum, count, etc are not permited but in having clause
the error is the "..sum.." in the "were" clause

Sorry you are right. I was editing a real querie that I had generated which used a 'group by', 'having' clause. I was editing it to post a statement consistent with the hypothetical one I have been refering to in this thread, and I forgot to take out the SUM(). Well spotted.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum