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...