vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB4 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=251)
-   -   SQL Query for number of posts per user group (https://vborg.vbsupport.ru/showthread.php?t=320567)

kgroneman 10-19-2015 02:20 PM
SQL Query for number of posts per user group
 
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:

Code:
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 10-19-2015 02:28 PM
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 10-19-2015 03:23 PM
I think you want something like this:
Code:
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 10-19-2015 06:48 PM
Quote:
Originally Posted by Dave (Post 2557287)
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] ---------------

Quote:
Originally Posted by kh99 (Post 2557291)
I think you want something like this:
Code:
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:

Code:
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 10-19-2015 07:14 PM
Try:

HTML Code:
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 10-19-2015 07:15 PM
I found it! Thanks to your help and guidance:

Code:
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?


All times are GMT. The time now is 12:59 PM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01106 seconds
  • Memory Usage 1,738KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (5)bbcode_code_printable
  • (1)bbcode_html_printable
  • (2)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (6)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete