Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SELECT AVG

Posted on 2011-03-17
22
Medium Priority
?
381 Views
Last Modified: 2012-05-11
How can I get this code to work?
SELECT eod, AVG(SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date) as avgeod, CONVERT(char(10), Date, 101) AS FormatedDate
FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7

Open in new window

0
Comment
Question by:aka_FATCAT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 3
  • +1
22 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35154222
first, you need to clarify what exactly you want as output, based on the input ....
the formula indeed won't work, syntax might be like below.
check out that the subquery shall have a ORDER BY, otherwise the TOP will not result in consistent results, eventually
SELECT eod
, CONVERT(char(10), Date, 101) AS FormatedDate
, (select AVG(eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date ORDER BY cf.date DESC ) ) as avgeodFROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7

Open in new window

0
 

Author Comment

by:aka_FATCAT
ID: 35154293
Getting the same error:

Incorrect syntax near ')'.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154305
SELECT eod
, CONVERT(char(10), Date, 101) AS FormatedDate
, (select AVG(eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date ORDER BY cf.date DESC ) ) as avgeod FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:aka_FATCAT
ID: 35154312
Getting the same error:

Incorrect syntax near ')'.
SELECT eod, CONVERT(char(10), Date, 101) AS FormatedDate, (select AVG(eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date ORDER BY cf.date DESC ) ) as avgeod
FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154335
is this working

SELECT eod, CONVERT(char(10), Date, 101) AS FormatedDate
FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7
0
 

Author Comment

by:aka_FATCAT
ID: 35154358
Yup.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154361
is this working seperatly

SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date ORDER BY cf.date DESC
0
 

Author Comment

by:aka_FATCAT
ID: 35154363
The multi-part identifier "c.date" could not be bound.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154366
try this
SELECT eod, CONVERT(char(10), Date, 101) AS FormatedDate,
(select AVG(X.eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.date ORDER BY cf.date DESC ) X )
as avgeod
FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154379
try this
SELECT eod, CONVERT(char(10), Date, 101) AS FormatedDate,
(select AVG(X.eod) FROM (SELECT TOP 4 eod from cash_flow cf ORDER BY cf.date DESC ) X )
as avgeod
FROM cash_flow
WHERE DATEPART(WEEKDAY, date) = 7
0
 

Author Comment

by:aka_FATCAT
ID: 35154423
This is close, but its only right the first four records, after that it just repeats the last number.
SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate, 
(select AVG(X.eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.Date AND DATEPART(WEEKDAY, date) = 7 ORDER BY cf.date ASC) X ) 
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 7
ORDER BY date asc

Open in new window

0
 

Expert Comment

by:nirmalrampk
ID: 35154437
What is your exact requirement, can you ellabrate your requirement
0
 

Author Comment

by:aka_FATCAT
ID: 35154720
I have an amount entered daily. (eod)
I would like to calculate the moving average based on the last month (4 weeks).

So I call on the db to return all the entries on a given weekday (i.e. Saturday)

Then I ask for the previous 4 entries on (i.e. Saturday) and avg that data.

After which I would like it to look at the next entry and compare that to the previous 4 (i.e. Saturdays), based on that entries date.

table = cash_flow
column1 = eod
column2 = date

Basically I'm looking for a 4 week moving average based on the day of the week.

The code is working, but only for the first four entries.

Here are the results of the current query.

eod                      FormatedDate         avgeod
11843.3900      09/11/2010      11843.3900
9180.1900      09/18/2010      10511.7900
9171.2500      09/25/2010      10064.9433
10055.7700      10/02/2010      10062.6500
11632.9900      10/09/2010      10062.6500
11233.7900      10/16/2010      10062.6500
10403.0200      10/23/2010      10062.6500
13332.5300      10/30/2010      10062.6500
11370.6000      11/06/2010      10062.6500
13590.4400      11/13/2010      10062.6500
SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate, 
(select AVG(X.eod) FROM (SELECT TOP 4 eod from cash_flow cf where cf.date <= c.Date AND DATEPART(WEEKDAY, date) = 7 ORDER BY cf.date ASC) X ) 
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 7
ORDER BY date asc

Open in new window

0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 35154811
remove top 4

try this

 SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate,
(select AVG(X.eod) FROM (SELECT eod from cash_flow cf where cf.date <= c.Date AND DATEPART(WEEKDAY, date) = 7 ORDER BY cf.date ASC) X )
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 7
ORDER BY date asc
0
 

Author Comment

by:aka_FATCAT
ID: 35154877
It throws the following error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
0
 

Author Comment

by:aka_FATCAT
ID: 35154913
I removed the first ORDER BY and it worked perfectly.

Thank you.
SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate, 
(select AVG(X.eod) FROM (SELECT eod from cash_flow cf where cf.date <= c.Date AND DATEPART(WEEKDAY, date) = 5) X ) 
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 5
ORDER BY date asc

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35154932
try this

SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate,
(select AVG(X.eod) FROM (SELECT
Top( select count(*) From cash_flow ct where ct.date <= c.Date AND DATEPART(WEEKDAY, date) = 7)
 eod from cash_flow cf where cf.date <= c.Date AND DATEPART(WEEKDAY, date) = 7 ORDER BY cf.date ASC) X )
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 7
ORDER BY date asc
0
 

Author Comment

by:aka_FATCAT
ID: 35154946
Working chart.
aka-FATCAT-429747.flv
0
 

Expert Comment

by:nirmalrampk
ID: 35154988
hi,
 If you requirement is
 "I would like to calculate the moving average based on the last month (4 weeks)"

previous query is not correct, becuase it will calculate average for all previous values not last month (last 4 weeks)

0
 

Author Comment

by:aka_FATCAT
ID: 35155021
You're actually right. So now what do I do?
0
 

Expert Comment

by:nirmalrampk
ID: 35155190
Hi,
 Try this

this query will return all Sunday with previous four Sundays avg.

SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate
      ,(SELECT avg(ct.eod)  FROM cash_flow ct
            where ct.date > dateadd(d,-4*7,c.Date)
                  AND ct.date < c.Date
                  AND DATEPART(WEEKDAY, date) = 7)
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 7
0
 

Author Comment

by:aka_FATCAT
ID: 35162378
This code seems to be working just as I wanted.
SELECT eod, CONVERT(char(10), c.Date, 101) AS FormatedDate,
(select AVG(X.eod) FROM
 ( SELECT eod from cash_flow cf
     where cf.date <= c.Date
     AND DATEPART(WEEKDAY, date) = 1
     AND (c.Date - cf.date) < 28
  ) X )
as avgeod
FROM cash_flow c
WHERE DATEPART(WEEKDAY, date) = 1
ORDER BY date asc

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question