Add Column Of Days From Date

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

noctorum
Forum Commoner
Posts: 31
Joined: Fri Jun 13, 2008 10:46 am

Re: Add Column Of Days From Date

Post 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)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Add Column Of Days From Date

Post by Christopher »

I don't think you can use 'count' as a column name without quoting because it is a reserved word.
(#10850)
noctorum
Forum Commoner
Posts: 31
Joined: Fri Jun 13, 2008 10:46 am

Re: Add Column Of Days From Date

Post 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.
noctorum
Forum Commoner
Posts: 31
Joined: Fri Jun 13, 2008 10:46 am

Re: Add Column Of Days From Date

Post by noctorum »

Solved

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