Jump to content

Archived

This topic is now archived and is closed to further replies.

DarkReaper

Grouping by days

Recommended Posts

Hello i have a table with structure:
[code]
  `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`)

[/code]

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.

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

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

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

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
[code]SELECT count(id) as total FROM uniques WHERE site_id=2 GROUP BY TO_DAYS(FROM_UNIXTIME(uniques.time));[/code]
Does the trick. Case closed :)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
I'm not sure I understand what you mean.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
So you simply want to combine the individual queries from each table?

Share this post


Link to post
Share on other sites
Assuming that you select the columns appropriately, UNION will allow you to do this.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.