asked on

AVERAGE Count in 4.1.16

This query works in Mysql but I need it to work in 4.1.16
when I run it I get
1064 check right syntax to use near '(`count`) FROM (SELECT DATE_FORMAT(`date_placed`,'%W %d %M %Y'), COUNT(*)' at line1

SELECT AVG(`count`) from (
SELECT DATE_FORMAT( `date_placed` , '%W %d %M %Y' ) , COUNT( * ) `count`
FROM (
SELECT `date_placed`
FROM `orders`
WHERE `date_placed` >= Date_Sub(CURDATE(), INTERVAL 1 WEEK)
) AS tmp
GROUP BY DATE_FORMAT( `date_placed` , '%W %d %M %Y' )
) as l

I would like a query to return the average of the order count for the entire week. Also I would like a query that returns the average order count for the week from last year. So Date_Sub(CURDATE(), INTERVAL 1 WEEK) would be Date_Sub(CURDATE() **MINUS 365 days**, INTERVAL 1 WEEK)

test data
order_id date_placed
0076619 2006-03-09 14:46:46
0075994 2006-02-15 21:52:59
0075997 2006-02-16 09:35:14
0076008 2006-02-16 12:21:37

Thanks for any help.

star_trek

try this you missed a , after Count(*)
SELECT AVG(`count`) from (
SELECT DATE_FORMAT( `date_placed` , '%W %d %M %Y' ) , COUNT( * ), `count`
FROM (
SELECT `date_placed`
FROM `orders`
WHERE `date_placed` >= Date_Sub(CURDATE(), INTERVAL 1 WEEK)
) AS tmp
GROUP BY DATE_FORMAT( `date_placed` , '%W %d %M %Y' )
) as l

akshah123

I have version 4.1.12 and the query seems to work just fine on my machine. (Except for the 1 week part, which i changed to 7 day.)

Scott_Edge

ASKER

I still get a syntax error near

1 WEEK)
) AS tmp
GROUP BY DATE_FORMAT( `date_placed` , '%W %d %M %Y' )
) as l

Scott_Edge

ASKER

akshah123 It seems the week interval was what was the problem.

star_trek unfortunetly the query doesn't work with the comma after COUNT(*) thank you for your help though.

Do you know how I can select for the previous years week?

ASKER CERTIFIED SOLUTION

akshah123

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

star_trek

SELECT AVG(`count`) from (
SELECT DATE_FORMAT( `date_placed` , '%W %d %M %Y' ) , COUNT( * ), `count`
FROM (
SELECT `date_placed`
FROM `orders`
WHERE `date_placed` >= Date_Sub(CURDATE(), INTERVAL 7 DAY)
) AS tmp
GROUP BY DATE_FORMAT( `date_placed` , '%W %d %M %Y' )
) as l