Counting with properties - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Counting with properties

Author

Topic

quirinus
Starting Member

3 Posts

Posted - 2011-03-14 : 10:43:19

Hello.

I'm using WinXP SP2 with WampServer 2.0, phpMyAdmin for viewing/editing the databases/tables. MySQL 5.1 MySQL Cluster NDB 6.X/7.X
Don't know what version of SQL I'm using.

I've got a table with unique user sessions, like so:

CREATE TABLE IF NOT EXISTS `guest` (
`unique` int(11) NOT NULL,
`session_user_id` int(11) NOT NULL,
`autologin` int(11) NOT NULL,
`viewonline` int(11) NOT NULL,
`last_time` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

With sample data:

INSERT INTO `guest` (`unique`, `session_user_id`, `autologin`, `viewonline`, `last_time`) VALUES
(1, 2, 1, 1, 1300108710),
(3, 53, 1, 1, 1300108750),
(4, 2, 1, 1, 1300108350),
(5, 2, 1, 1, 1300108730),
(2, 2, 1, 1, 1300108710);

I want to see how many different users (identified by session_user_id) that have the session_user_id>1, viewonline=1 and that have visited the page not longer that 300 seconds ago (if the current time is, let's say 1300108810) from (the largest/last) last_time.

I think this is it:

SELECT COUNT(DISTINCT session_user_id) FROM guest WHERE session_user_id > 1 AND viewonline = 1 AND ((1300108810 - last_time)<=300)

It gives 2 as a result.

In this case it should find all the session rows that are less than 300s ago, and then count the ones that have unique user_id, giving the result without using the last/largest last_time whatsoever. Is that correct?

Is there any better/other way to do it?

I've tried this, later:

SELECT COUNT(DISTINCT session_user_id) FROM guest WHERE session_user_id > 1 AND viewonline = 1 GROUP BY user_id HAVING ((1300108510 - MAX(last_time))<=300))

It returns 2 rows with 1 in each row.

I assume this query is not correct since MAX needs a SELECT executed before it to work? So MAX is ignored and just some (random from the group?) last_time inside it is used? But HAVING shouldn't work with just one column specified because it needs a function that connects (aggregates?) rows in the column? Then it counts unique user_ids for each group(???); and since there is now only 1 row for each user because of grouping, it again, gives 2 rows with counts of user2 = 1 and user53 = 1?

Any way to do it with(out) nested SELECTS and with HAVING? Or some third way?

Could someone please confirm/deny all this (or a part of it)? And sorry for all the question marks, I'm not sure about many things. Thank you in advance.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-14 : 11:16:11

This is a Microsoft SQL Server forum, but I think the value 1300108810 changes with time, no?

SELECT COUNT(DISTINCT session_user_id)
FROM guest
WHERE session_user_id > 1 AND viewonline = 1

and (select 1300108510 - max(last_time) from guest) <=300

Jim

Everyday I learn something that somebody else already knew

quirinus
Starting Member

3 Posts

Posted - 2011-03-14 : 11:44:47

Yes, it changes with time, but I just put that time for testing purposes. I am sorry for posting in the wrong forum then. Thank you for answering despite that.

Does that find the MAX(last_time) for each unique session_user_id? Because I need to find the MAX(last_time) for each unique session_user_id and then count the unique users that have (current_time_variable_integer_output - max(last_time)) <= 300.

Thank you very much.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-14 : 12:13:07

If I'm understanding you correctly, things just got easier!

SELECT COUNT(DISTINCT session_user_id)
FROM guest g1
WHERE session_user_id > 1 AND viewonline = 1

This will give you the unique number of session_user_ids that have visited within the last 300.
Since we're counting uniquely, we don't care how many visits each user has, as long as they have at least one visit within 300s

Jim

Everyday I learn something that somebody else already knew

quirinus
Starting Member

3 Posts

Posted - 2011-03-14 : 12:30:24

quote:
Originally posted by jimf

If I'm understanding you correctly, things just got easier!

SELECT COUNT(DISTINCT session_user_id)
FROM guest g1
WHERE session_user_id > 1 AND viewonline = 1

This will give you the unique number of session_user_ids that have visited within the last 300.
Since we're counting uniquely, we don't care how many visits each user has, as long as they have at least one visit within 300s

Jim

Everyday I learn something that somebody else already knew

Yes, that is what I want:
If the unique user has any session in the last 300 seconds, he should be counted (as online).

But there's apparently a part of the code missing in your post.

EDIT: Got everything working with a bit roundabout method, will try a better approach later. Thank you for your assistance, it has been very helpful. Have a nice day. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources