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

Add Column Of Days From Date

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

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

Re: Add Column Of Days From Date

Post by noctorum »

For simplicity's sake in forming queries, is there a way to add the column?
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 »

What does add a column to the query results, but does not add a column to the database table.
(#10850)
noctorum
Forum Commoner
Posts: 31
Joined: Fri Jun 13, 2008 10:46 am

Re: Add Column Of Days From Date

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

Re: Add Column Of Days From Date

Post by noctorum »

bump
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 »

Code: Select all

SELECT COUNT( * ) , DAYNAME( `date` ) AS day FROM phonelog2 WHERE DAYNAME( `date` )="Monday"
(#10850)
noctorum
Forum Commoner
Posts: 31
Joined: Fri Jun 13, 2008 10:46 am

Re: Add Column Of Days From Date

Post 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
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 »

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)
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: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.
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 »

Then GROUP BY `date`
(#10850)
noctorum
Forum Commoner
Posts: 31
Joined: Fri Jun 13, 2008 10:46 am

Re: Add Column Of Days From Date

Post 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
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 »

day, FROM phonelog2
(#10850)
noctorum
Forum Commoner
Posts: 31
Joined: Fri Jun 13, 2008 10:46 am

Re: Add Column Of Days From Date

Post 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
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 »

Now you have two FROM clauses...
(#10850)
Post Reply