Page 1 of 2

Add Column Of Days From Date

Posted: Tue Jun 17, 2008 10:37 am
by noctorum
I have a column in my DB entitled 'Date'. I'd like to add another column that contains the day of the week (Monday, Tuesday etc.) generated from the date as the DB has 40,000~ entries, but I'm not quite sure how to go about it?

edit: The date column is in MySQL's date format, YYYY-MM-DD.

Re: Add Column Of Days From Date

Posted: Tue Jun 17, 2008 12:15 pm
by Christopher
You don't need to add a column to the database. MySQL has both DAYOFWEEK(date) and WEEKDAY(date) functions the former gives 1-7, the latter 0-6. Just do:

Code: Select all

SELECT `date`, WEEKDAY(`date`) AS mydaynumber ...

Re: Add Column Of Days From Date

Posted: Wed Jun 18, 2008 1:49 pm
by noctorum
For simplicity's sake in forming queries, is there a way to add the column?

Re: Add Column Of Days From Date

Posted: Wed Jun 18, 2008 2:03 pm
by Christopher
What does add a column to the query results, but does not add a column to the database table.

Re: Add Column Of Days From Date

Posted: Thu Jun 19, 2008 2:18 pm
by noctorum
Then I run into this problem;

Code: Select all

SELECT COUNT( * ) , DAYNAME( `date` ) AS day FROM phonelog2 WHERE day="Monday"
#1054 - Unknown column 'day' in 'where clause'

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 11:22 am
by noctorum
bump

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 11:45 am
by Christopher

Code: Select all

SELECT COUNT( * ) , DAYNAME( `date` ) AS day FROM phonelog2 WHERE DAYNAME( `date` )="Monday"

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 12:57 pm
by noctorum
Still gets an error;
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 1:07 pm
by Christopher
You slipped COUNT(*) in there mid discussion. Yes, logically you need GROUP BY if you are going to use COUNT() with other columns. What do you want the results to look like?

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 1:58 pm
by noctorum
arborint wrote:You slipped COUNT(*) in there mid discussion. Yes, logically you need GROUP BY if you are going to use COUNT() with other columns. What do you want the results to look like?
I'm trying to return the total number of rows each day (sun-sat) so I can calculate an average per day.

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 2:47 pm
by Christopher
Then GROUP BY `date`

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 4:55 pm
by noctorum
It seems there would be some way to slip in an AVG(Count)? I tried;

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
On the recommendation of a friend, but that returns;
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT( * ) as count , DAYNAME( `date` ) AS day, FROM pho

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 5:12 pm
by Christopher
day, FROM phonelog2

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 5:20 pm
by noctorum
Still won't take it, same error;

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
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT( * ) as count , DAYNAME( `date` ) AS day FROM phon

Re: Add Column Of Days From Date

Posted: Fri Jun 20, 2008 5:28 pm
by Christopher
Now you have two FROM clauses...