Link to home
Start Free TrialLog in
Avatar of rp
rpFlag for Portugal

asked on

Calculate average

Hi,

I need a query to return the average per day for each customer and total average of all customers per day. How can i do that?

I can calculate the average for each date/client but how can calculate (and add a row) with the average of the day .

select Avg(Value) as Med , Customername as customer, Datea as dateinvoice from AGH group by datea,Customer

Attached, image with example what i need.

best regards
Img14.png
Avatar of ste5an
ste5an
Flag of Germany image
Use the WITH {ROLLUP|CUBE} clause:

SELECT   AVG([Value]) AS Med ,
         Customername AS customer ,
         Datea AS dateinvoice
FROM     AGH
GROUP BY datea ,
         Customer WITH ROLLUP;

Open in new window


btw, [Value] is a reserved word and should not be used as column name.
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rp
rp
Flag of Portugal image

ASKER

Hi Mark Wills,

The query provided by you and ste5an works as i need, however calculating grand total is better yet. Please, can you provide query to calculate grand total also.

best regards
Avatar of Mark Wills
Mark Wills
Flag of Australia image
Same query, just comment out the HAVING....
Avatar of Mark Wills
Mark Wills
Flag of Australia image
How are you going with this ?