Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 19 Oct 2015, 17:20
kgroneman's Avatar
kgroneman kgroneman is offline
 
Join Date: Aug 2007
Question 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:


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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?
Reply With Quote
  #2  
Old 19 Oct 2015, 17:28
Dave Dave is offline
 
Join Date: Jun 2010
Real name: 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.
__________________
https://technidev.com - security, development, exploits, vBulletin
dave[at]technidev[dot]com

Contact me for custom vBulletin 3/4 work & server/website management.
Reply With Quote
  #3  
Old 19 Oct 2015, 18:23
kh99 kh99 is offline
 
Join Date: Aug 2009
Real name: Kevin
I think you want something like this:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

Reply With Quote
  #4  
Old 19 Oct 2015, 21:48
kgroneman's Avatar
kgroneman kgroneman is offline
 
Join Date: Aug 2007
Originally Posted by Dave View Post
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 19 Oct 2015 at 21:50 ---------------

Originally Posted by kh99 View Post
I think you want something like this:

Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.
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 19 Oct 2015 at 22:11 ---------------

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


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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?
Reply With Quote
  #5  
Old 19 Oct 2015, 22:14
Dave Dave is offline
 
Join Date: Jun 2010
Real name: Dave
Try:


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

__________________
https://technidev.com - security, development, exploits, vBulletin
dave[at]technidev[dot]com

Contact me for custom vBulletin 3/4 work & server/website management.
Reply With Quote
  #6  
Old 19 Oct 2015, 22:15
kgroneman's Avatar
kgroneman kgroneman is offline
 
Join Date: Aug 2007
I found it! Thanks to your help and guidance:


Block Disabled:      (Update License Status)  
Suspended or Unlicensed Members Cannot View Code.

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

--------------- Added 19 Oct 2015 at 22:24 ---------------

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



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 17:08.

Layout Options | Width: Wide Color: