SQL for SQL Query Designer

[sultan123]

I have the following SQL which works as SQL statment however it need to do a little bit more as mentioned below:

SELECT
Customer.Country, customer.[customer name], SUM(Orders.[Order Amount])AS [Sum Of Orders Placed]
FROM
Customer JOIN Orders ON customer.[Customer ID] = orders.[Customer ID]
GROUP by Customer.Country,customer.[customer name]
HAVING SUM(Orders.[Order Amount])>20000
ORDER BY Customer.Country ASC,customer.[customer name]ASC

BUT I want
HAVING SUM(Orders.[Order Amount])>
AVERAGE(HAVING SUM(Orders.[Order Amount]))

I want to pick customers who have ABOVE AVERAGE total SUM of orders placed grouped as above WITHOUT USING A VARIABLE.


Is it possible if so how?

Many thanks

 

[synapsevampire]

In general you should post SQL questions in a Database forum (this is a Crystal forum), and you might want to mention the type and version of the database.

Is that to say you want SUMs that are greater than the mean average SUM?

You don't really need to do it on the database if you're using Crystal.

You could use an average formula (drop the sum into the report, right click and select insert->grand total->Average). Now you can base suppression on this field.

In SQL you might use a subquery to obtain the average of the sums:

-k