Join and Aggregate Problem 1

[sultan123]

I have the following SQL which works..

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.


I can't work it out, can you please help?

 

[nigelrivett]

The having clause operates on each group. Your aveerage needs to work on all groups so I guess you would have to repeat the query to get the average.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[sultan123]

Hi Nigel

Sorry would u pleae explain what do you mean by:
'The having clause operates on each group. Your aveerage needs to work on all groups so I guess you would have to repeat the query to get the average.'

How can i do that?

 

[swampBoogie]

having SUM(Orders.[Order Amount]) >
(select avg(s) from
  (select SUM(Orders.[Order Amount])AS s
    FROM Customer JOIN Orders 
  ON customer.[Customer ID] = orders.[Customer ID]
GROUP by Customer.Country,customer.[customer name]) dt)

 

[sultan123]

Hi Nigel
That is wonderful work,

I have never seen this format before......
...having SUM(Orders.[Order Amount]) >
(select avg(s) from
(select SUM(Orders.[Order Amount])AS s

I thought you could only do this.....
...having SUM(Orders.[Order Amount]) >
(select SUM(Orders.[Order Amount])From tble

Which works but doesnt return what I want so i would have tried..

...having SUM(Orders.[Order Amount]) >
(select AVG(SUM(Orders.[Order Amount]))From tble

Which would return the correct value only it is not allowed ..!!


Many thanks i did learn something new and it is very valuable too.

 

[sultan123]

swampBoogie

my message was above is refered to you..