Jump to content


Photo

Grouping by days


  • Please log in to reply
6 replies to this topic

#1 DarkReaper

DarkReaper
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 22 September 2006 - 09:38 AM

Hello i have a table with structure:
  `id` int(10) NOT NULL auto_increment,
  `site_id` int(10) NOT NULL default '0',
  `time` int(10) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `site_id` (`site_id`)


My problem is that i want to select all entries by days. My date cell is time, and its unixtime stamp. I know how to select the entries from today ... but i want to group them by all the days available.

SELECT count(id) as total FROM uniques WHERE site_id=2 GROUP BY TO_DAYS(FROM_UNIXTIME(TIME));

This code returns all the entries from today. Because of the "TIME" code. I've tried also:

SELECT count(id) as total FROM uniques WHERE site_id=2 GROUP BY TO_DAYS(FROM_UNIXTIME(uniques.time));

But the result was the same. Helppp :)

Short version: I have the table from above, i want to select all entries grouped by days. My time stamp is the unix default and its stored in the cell -> time.


--------------------


WHOOPS! My bad. I've forgotten to cycle though the returned rows ... sorry the
SELECT count(id) as total FROM uniques WHERE site_id=2 GROUP BY TO_DAYS(FROM_UNIXTIME(uniques.time));

Does the trick. Case closed :)

#2 DarkReaper

DarkReaper
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 22 September 2006 - 09:51 AM

My last problem is that i have 3 tables, and i need the data from them to be merged by this sql query.

The tables are as follows uniques, impr, vis. I need to extract the same data at the same time from them, with 1 query. I know how to do it with multiple queries but i dont like that way, its too uniefficient.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 September 2006 - 10:09 AM

I'm not sure I understand what you mean.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 DarkReaper

DarkReaper
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 22 September 2006 - 10:25 AM

I have 3 tables. Each of them contains information for a specified period of time by the column "time".
I want to print for example the information from the 3 tables for yesterday/2 weeks ago or any other date i want.

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 September 2006 - 10:29 AM

So you simply want to combine the individual queries from each table?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 DarkReaper

DarkReaper
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 22 September 2006 - 10:44 AM

Yes.

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 September 2006 - 11:21 AM

Assuming that you select the columns appropriately, UNION will allow you to do this.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users