SQL Query for number of posts per user group

[kgroneman]

Hi,

I'm trying to get the number of posts for users in a specific user group in a specific timeframe and am using this query:

SELECT posts, username, email from vb_user
WHERE (usergroupid=10 OR usergroupid=62)
AND dateline > UNIX_TIMESTAMP('2015-06-30')
AND dateline < UNIX_TIMESTAMP('2015-10-01')
ORDER BY posts
DESC

However, I get the following error regarding "dateline"

An error occurred while attempting to execute your query. The following information was returned.
error number: 1054
error desc: Unknown column 'dateline' in 'where clause'

Can anyone help me with this? I have four 4.2.2 PL4 forum sets and this happens on all four

If I take out the dateline sections, it works, but it gives me stats for all time, not just the date range I'm looking for. What am I doing wrong?

[Dave]

The user table does not have a dateline column, that's why it doesn't work.
You can use: joindate, lastvisit, lastactivity, lastpost.

By the way it's easier to write WHERE usergroupid IN (10, 62) for the first WHERE statement.

[kh99]

I think you want something like this:

SELECT COUNT(*) FROM vb_post 
   LEFT JOIN vb_user ON vb_user.userid = vb_post.userid 
   WHERE vb_user.usergroupid IN (10,62)
      AND visible = 1
      AND dateline > UNIX_TIMESTAMP('2015-06-30')
      AND dateline < UNIX_TIMESTAMP('2015-10-01')

[kgroneman]

Originally Posted by Dave

The user table does not have a dateline column, that's why it doesn't work.
You can use: joindate, lastvisit, lastactivity, lastpost.

By the way it's easier to write WHERE usergroupid IN (10, 62) for the first WHERE statement.

Thanks Dave. The options you list won't give me statistics for the users in that group for JUST that time period which is what I'm looking for. Thanks for the response and thanks for the shortcut for usergroup.

--------------- Added [DATE]1445287851[/DATE] at [TIME]1445287851[/TIME] ---------------

Originally Posted by kh99

I think you want something like this:

SELECT COUNT(*) FROM vb_post 
   LEFT JOIN vb_user ON vb_user.userid = vb_post.userid 
   WHERE vb_user.usergroupid IN (10,62)
      AND visible = 1
      AND dateline > UNIX_TIMESTAMP('2015-06-30')
      AND dateline < UNIX_TIMESTAMP('2015-10-01')

Thanks for the help. That only gives me a total number for those usergroups which isn't exactly what I'm looking for. I'm trying to get a list of users in those specific user groups along with the number of posts per each user for that time period. I'm not very good at SQL queries (which must be obvious). I appreciate the response.

--------------- Added [DATE]1445289086[/DATE] at [TIME]1445289086[/TIME] ---------------

I've been playing around with it a bit and I've almost got the following to work:

SELECT COUNT(postid) AS count, user.username
FROM post
LEFT JOIN user ON (user.userid = post.userid)
WHERE (dateline > UNIX_TIMESTAMP('2015-06-31') AND dateline < UNIX_TIMESTAMP('2015-10-01'))
AND (usergroup='10')
GROUP BY post.username
ORDER BY count
DESC

If I take out the "AND (usergroup='10')" line it works but it's for ever user. How do I get it to work for just a single usergroup?

[Dave]

Try:

SELECT COUNT(*) AS posts, post.userid
FROM post
LEFT JOIN user ON post.userid = user.userid
WHERE user.usergroupid IN (10, 62) 
AND visible = 1
AND dateline BETWEEN UNIX_TIMESTAMP( '2015-06-30') AND UNIX_TIMESTAMP( '2015-10-01') 
GROUP BY post.userid
ORDER BY posts DESC

[kgroneman]

I found it! Thanks to your help and guidance:

SELECT COUNT(postid) AS count, user.username
FROM post
LEFT JOIN user ON (user.userid = post.userid)
WHERE (dateline > UNIX_TIMESTAMP('2015-06-31') AND dateline < UNIX_TIMESTAMP('2015-10-01'))
AND user.usergroupid IN (10)
GROUP BY post.username
ORDER BY count
DESC

It does just what I want. Thanks so much for taking the time to respond.

--------------- Added [DATE]1445289865[/DATE] at [TIME]1445289865[/TIME] ---------------

Ok, one last (I hope) question: How do I get email addresses to also show along with the user ID in the resultant list?