Page 2 of 2

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 5:32 pm
by noctorum
arborint wrote:Now you have two FROM clauses...
Shouldn't there be? One for the main query and one for the derived table? (thanks for all the help so far btw)

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 7:28 pm
by Christopher
I don't think you can use 'count' as a column name without quoting because it is a reserved word.

Re: Add Column Of Days From Date

Posted: Sun Jun 22, 2008 11:16 am
by noctorum
arborint wrote:I don't think you can use 'count' as a column name without quoting because it is a reserved word.
Tried it a few different ways;

Code: Select all

SELECT DAYNAME( `date` ) AS day, AVG(theCount) FROM (SELECT COUNT( * ) as theCount , DAYNAME( `date` ) AS day FROM phonelog2 WHERE DAYNAME( `date` )="Monday" GROUP BY `date`) phonelog2

Code: Select all

SELECT DAYNAME( `date` ) AS day, AVG('Count') FROM (SELECT COUNT( * ) as 'Count' , DAYNAME( `date` ) AS day FROM phonelog2 WHERE DAYNAME( `date` )="Monday" GROUP BY `date`) phonelog2

Code: Select all

SELECT DAYNAME( `date` ) AS day, AVG('theCount') FROM (SELECT COUNT( * ) as 'theCount' , DAYNAME( `date` ) AS day FROM phonelog2 WHERE DAYNAME( `date` )="Monday" GROUP BY `date`) phonelog2
All gave a syntax error.

Re: Add Column Of Days From Date

Posted: Sun Jun 22, 2008 2:48 pm
by noctorum
Solved

For anyone that needs reference in the future, subqueries/derived tables do not work in MySQL versions older than 4.1.