Add Column Of Days From Date
Moderator: General Moderators
Add Column Of Days From Date
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.
edit: The date column is in MySQL's date format, YYYY-MM-DD.
- 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
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 ...(#10850)
Re: Add Column Of Days From Date
For simplicity's sake in forming queries, is there a way to add the column?
- 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
What does add a column to the query results, but does not add a column to the database table.
(#10850)
Re: Add Column Of Days From Date
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'
- 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
Code: Select all
SELECT COUNT( * ) , DAYNAME( `date` ) AS day FROM phonelog2 WHERE DAYNAME( `date` )="Monday"(#10850)
Re: Add Column Of Days From Date
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
- 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
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?
(#10850)
Re: Add Column Of Days From Date
I'm trying to return the total number of rows each day (sun-sat) so I can calculate an average per day.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?
- 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
It seems there would be some way to slip in an AVG(Count)? I tried;
On the recommendation of a friend, but that returns;
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 pho
- 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
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
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US