Can't Join Table on Same Table

[unemployment]

I am trying to join a views table which holds the number of times a page has been viewed. I'm trying to return two things, the total views for each page / location and the total trending views over the past month for the same result set.

 

My query is:

 

SELECT
  a.location_id,
  a.location_type,
  a.total_views,
  b.location_id,
  b.location_type
FROM
  (SELECT
   views1.location_id,
   views1.location_type,
   COUNT(views1.location_id) as total_views
  FROM views as views1
  GROUP BY views1.location_id, views1.location_type
 ) as a,
(SELECT
   views2.location_id,
   views2.location_type
  FROM views as views2
  GROUP BY views2.location_id, views2.location_type
 ) as b
GROUP BY a.location_id, a.location_type

But that outputs...

 

http://awesomescreenshot.com/0e7wg8q5b

 

Notice that the location_id and location_type columns should be the same. I'm not sure why there aren't. Any ideas?

Edited February 8, 2013 by Zane

[Psycho]

Because you are not defining any JOIN condition so it is doing a dross-join - joining EVERY record from the first table on EVERY record from the 2nd. You wouldn't need to have the location_id listed twice anyway (if it is done correctly). That query is also a little more complicated than I think it needs to be. I think there's a good solution, but I need to verify it first.

 

Can you provide a more detailed description of exactly what data you are wanting? The queries you have don't quite make sense to me.

Edited February 7, 2013 by Psycho

[mikosiko]

As Psycho said... more information could help to give you a more precise answer, however reading your post seems to me that you are looking something like this? (modify it according to your needs)

select location_id,
         location_type,
        COUNT(location_id)AS TotViews,
        SUM(IF(DATE_FORMAT(fecha,'%Y%m') = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1     MONTH),'%Y%m'),1,0)) As ViewsLastMonth,
       SUM(IF(DATE_FORMAT(fecha,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m'),1,0)) As TrendThisMonth
FROM views
GROUP BY location_id,location_type;

Edited February 7, 2013 by mikosiko

[unemployment]

As Psycho said... more information could help to give you a more precise answer, however reading your post seems to me that you are looking something like this? (modify it according to your needs)

select location_id,
location_type,
COUNT(location_id)AS TotViews,
SUM(IF(DATE_FORMAT(fecha,'%Y%m') = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),'%Y%m'),1,0)) As ViewsLastMonth,
SUM(IF(DATE_FORMAT(fecha,'%Y%m') = DATE_FORMAT(NOW(),'%Y%m'),1,0)) As TrendThisMonth
FROM views
GROUP BY location_id,location_type;

 

What is fecha?

[DaveyK]

Hey guys, I am also working on the same project and I have tried numerous options for the query (UNION, mulitple SELECTs inside the FROM and a couple of JOINs).

Allow me to explain. I am not a really good SQL programmer or anything so please bare with us.

 

We are tracking page views inside a table:

 

Views

View_id (AI)

viewer_id (INT)

location_id (INT)

location_type (INT)

time (INT)(UNIX_TIMESTAMP)

 

the View_id is obv the unqiue key in this table. Tje viewer_id is the id of the user that viewed the page, or 0 for guests. the location id is the ID of the location displayed, but since we have several types of locations (say, 6 different types), I chose to store the type also instead of using 6 different tables. so the combination of location_id and location_type is important, thought (in this table) obviously not unique.

 

Regardless, the result we would want is(I am most familiar with php arrays so);

 

[0] =>(

[location_id] => 1,

[location_type] => 1,

[total_views] => 89,

[views_since_yesterday] => 60

),

[1] =>(

[location_id] => 1,

[location_type] => 3,

[total_views] => 140,

[views_since_yesterday] => 10

),

[2] =>(

[location_id] => 2,

[location_type] => 4,

[total_views] => 200,

[views_since_yesterday] => 9

)

 

I hope I am getting the point accross here. Its supposed to be something of a "Trending Locations" kind of thing.

I'm sorry for the confusion before!

 

@mikosiko - I have no idea what you just wrote... But thanks anyway!

Edited February 7, 2013 by DaveyK

[Psycho]

Yeah, it is a lot simpler than you guys are trying:

 

SELECT `location_id`, `location_type`,
 COUNT(`view_id`) as `total_views`,
 SUM(`view_date` > DATE_SUB(NOW(), INTERVAL 7 DAY)) as `last_week_views`

FROM `views`

GROUP BY `location_id`, `location_type`

 

You can modify the condition in the SUM() as needed for different periods. So, if you want views since yesterday you need to create the date object for midnight for the prior day instead of just DATE_SUB -1 days.

Edited February 7, 2013 by Psycho

[DaveyK]

Hey psycho...

 

well, that just blew my mind. In simplicity. Oh well, I have a lot to learn.

 

Anyway, it does not quite work yet. I think I understand the DATE_SUB thing (I admint, I just looked it up!) but not matter what I try, it seems to return 0. Is this because the `view_date` (as you use it) is actually `time` (a UNIX_TIMESTAMP). So my two questions here are:

 

1. does this fail because we are storing unix_timestamps rather than actual dates

2. should we be using dates rather than unix_timestamps?

 

We very much appreciate your help!

[Psycho]

EDIT: Just saw your last response. I tested my queries using a timestamp field - so I don't know why you are not getting the correct results.

 

EDIT#2: Ok, I just reread your last response. Are you storing PHP timestamps or using the MySQL timestamps. You should definitely be using the MySQL timestamps so you can use the date operations in MySQL. In fact, you can set up the field to auto-populate with the current timestamp when creating records in the views table. So, you would not need to include that field when doing INSERTs. Can you provide the table format for the views table?

 

OK, after a little googling, this will work for getting the correct count of 'views_since_yesterday' for all views that occurred since midnight at the beginning of the previous day

 

SELECT `location_id`, `location_type`,
 COUNT(`view_id`) as `total_views`,
 SUM(`view_date` > DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY)) as `last_day_views`

FROM `views`

GROUP BY `location_id`, `location_type`

Edited February 7, 2013 by Psycho

[DaveyK]

Interesting, that is actually pretty great. We are currently using

 

<?php
$since_yesterday = $time - 86400;
$sql = "SELECT
 `location_id`,
 `location_type`,
 COUNT(`view_id`) as `total_views`,
 SUM(`time` > {$since_yesterday}) as `last_day_views`
   FROM `views`
   GROUP BY `location_id`, `location_type`";
?>

 

which appears to work nicely.

 

However, my question remains:

 

(...)

 

2. should we be using dates rather than unix_timestamps?

 

(...)

[DaveyK]

DESCRIBE `views`

 

| Field | Type | Null | Key | Default | Extra

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

| view_id | int(11) | NO | PRI | NULL | auto_increment

| viewer_id | int(11)| NO| | NULL |

| location_id | int(11) | NO | | NULL |

| location_type | int(11) | NO | | NULL |

| time | int(11) | NO | | NULL |

 

EDIT: Sorry about the mess before!

 

EDIT2: WHen inserting `time` into the table I use the mySQL UNIX_TIMESTAMP() function to insert the time.

Edited February 7, 2013 by DaveyK

[Barand]

Don't store date/times as unix timestamps. You will always have to convert them before you can use the powerful Mysql date time functions.

 

Store, as you were told by Psycho, as Mysql DATETIME or TIMESTAMP. These are same formt (yyyy-mm-dd hh-ii-ss) but timestamps will update automatically on insert and update depending on the definition in the table creation.

Edited February 7, 2013 by Barand

[mikosiko]

What is fecha?

Sorry about that... it is your date/datetime/timestamp table field... I see that Psycho & Barand already straight the issue for you.

[DaveyK]

yeah. Thanks a bunch guys!

[Psycho]

As Barand stated you should absolutely be using the database timestamp field type. Right now you must be explicitly populating that value when a new view record is added to the table. By using the MySQL timestamp field you can let the database do that automatically.

 

So, to do this properly you will need to look at any operations that retrieve the date value for those records and change them as needed. It all depends on how you use the date in those scenarios.

[DaveyK]

In this case we are using backbone.js so almost everything is fluid js. Returning UNIX just made sense for real-time but, as I said, I have very little experience with this.

 

I will consider timestamp next time, thanks very much for the advice

[Psycho]

In this case we are using backbone.js so almost everything is fluid js. Returning UNIX just made sense for real-time

 

So, you are sending the unix timestamp from JS? Did you see what I posted twice previously that if you use a MySQL timestamp field you don't even need to pass the timestamp with the INSERT queries - it will be handled automatically by the database.

[unemployment]

So, you are sending the unix timestamp from JS? Did you see what I posted twice previously that if you use a MySQL timestamp field you don't even need to pass the timestamp with the INSERT queries - it will be handled automatically by the database.

 

How does the database store that automatically without adding it in the insert?

[Barand]

How does the database store that automatically without adding it in the insert?

 

By defining its default value to be the current time

[Psycho]

So, you are sending the unix timestamp from JS? Did you see what I posted twice previously that if you use a MySQL timestamp field you don't even need to pass the timestamp with the INSERT queries - it will be handled automatically by the database.

How does the database store that automatically without adding it in the insert?

 

As Barand stated you simply define its default value to be set to the current time. And, for future reference, you can also set up a timestamp field to automatically update to the current time when the record is modified. So no need to pass a time to the UPDATE queries to maintain a last updated value!

 

Here is something like what your table structure should look like

CREATE TABLE `views` (
 `view_id` int(11) NOT NULL auto_increment,
 `viewer_id` int(11) NOT NULL,
 `location_id` int(11) NOT NULL,
 `location_type` int(11) NOT NULL,
 `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
 PRIMARY KEY  (`view_id`)
)

Edited February 8, 2013 by Psycho

[DaveyK]

No I know that was possible, just never really know what was "Best practise".

 

AS for the UNIX, im sending the UNIX from sql->php-> js so that the JS can use it to show real-time times (IE: 1min ago)

However, I will considering using the setup as psycho suggested. Thanks!

[kicken]

You can use MySQL's UNIX_TIMESTAMP() to convert mysql's DATETIME or TIMESTAMP columns into a unix timestamp when you select them which you can then pass on to your javascript code.