Help please. avg help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help please. avg help

Author

Topic

rlopes
Starting Member

6 Posts

Posted - 2012-11-16 : 12:46:50

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
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-11-16 : 12:56:30

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

rlopes
Starting Member

6 Posts

Posted - 2012-11-16 : 12:57:35

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.

rlopes
Starting Member

6 Posts

Posted - 2012-11-16 : 13:00:41

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-16 : 13:27:32

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)

rlopes
Starting Member

6 Posts

Posted - 2012-11-16 : 13:36:49

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).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-16 : 13:55:04

See here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180719

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources