SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help please. avg help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rlopes
Starting Member

Portugal
6 Posts

Posted - 11/16/2012 :  12:46:50  Show Profile  Reply with Quote
hello everyone,
taking this example (http://www.w3schools.com/sql/sql_groupby.asp), how can i SUM order prices grouped by the month of the order date, and then return the total average by month.

hope anyone help me.
king regards

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1585 Posts

Posted - 11/16/2012 :  12:56:30  Show Profile  Reply with Quote
Instead of using OrderDate, use DateAdd(Month, DateDiff(Month, 0, OrderDate), 0). This does assume that OrderDate is a datetime.

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry

Edited by - Bustaz Kool on 11/16/2012 12:57:17
Go to Top of Page

rlopes
Starting Member

Portugal
6 Posts

Posted - 11/16/2012 :  12:57:35  Show Profile  Reply with Quote
in specific i have this query:
SELECT pa.nome as Cliente,AVG(mh.moh) as 'Média Horas Serviço 3M',AVG(mh.deh) as 'Média Kms Deslocação 3M'
FROM pa join sn on pa.snno=sn.snno join mh on mh.nopat=pa.nopat
WHERE sn.snno<>0 and (MONTH(pa.pdata))>(MONTH(getdate())-4)and (MONTH(pa.pdata))<(MONTH(getdate()))
GROUP BY pa.nome

But this returns me the AVG of all records, and i want the AVG of the months, in this case the last 3 months.
Go to Top of Page

rlopes
Starting Member

Portugal
6 Posts

Posted - 11/16/2012 :  13:00:41  Show Profile  Reply with Quote
quote:
Originally posted by Bustaz Kool

Instead of using OrderDate, use DateAdd(Month, DateDiff(Month, 0, OrderDate), 0). This does assume that OrderDate is a datetime.



Right. :) Thanks, but i still dont know how to find the AVG of the SUM by month.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/16/2012 :  13:27:32  Show Profile  Reply with Quote
Query below perhaps? I would also recommend changing the where clause as shown for couple of reasons.
--   If you don't want to group by Cliente,
--   remove it from the select list and the group by list.  
SELECT pa.nome      AS Cliente,
       DATEADD(mm, DATEDIFF(mm, 0, pa.pdata), 0) AS [FirstOfMonth],
       AVG(mh.moh)  AS 'Média Horas Serviço 3M',
       AVG(mh.deh)  AS 'Média Kms Deslocação 3M'
FROM   pa
       JOIN sn
            ON  pa.snno = sn.snno
       JOIN mh
            ON  mh.nopat = pa.nopat
WHERE  sn.snno <> 0
       AND pa.pdata >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) -4, 0)
       AND pa.pdata < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
GROUP BY
       pa.nome,
       DATEADD(mm, DATEDIFF(mm, 0, pa.pdata), 0)
Go to Top of Page

rlopes
Starting Member

Portugal
6 Posts

Posted - 11/16/2012 :  13:36:49  Show Profile  Reply with Quote
I do not explained myself well.

With that you get me the avg of sum of the months. Right.

But what i need its take that values and redo and AVG to get the AVG of the months.lol maybe with this simple example:

tableA
id Date Amount
1 10/11/2012 100
2 10/10/2012 100
2 11/10/2012 100
3 10/09/2012 100
4 10/08/2012 100

SELECT AVG(Amount) as Average FROM tableA

Well this query returns me an AVG value off all records (100). What i need its to know the AVG of the SUM of the MONTHS (125).
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/16/2012 :  13:55:04  Show Profile  Reply with Quote
See here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180719
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000