• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

SQL Query Help - How Many Topics / Threads Per Category

ProCom

Active member
#1
Hey code masters!

I have an advertiser that wants to know how many threads / topics there are in each forum "Category" and "Section".

Sure, I could just go through and count on my homepage, but I like learning new things and thought running a SQL query via phpMyAdmin that would bring up the data would be better... especially since I could also put time boundaries on the data, for example:

  • Topics Per Category - Past 180 days
  • Topics Per Forum - Past 500 days

... problem is that I'm FAR from a SQL expert... which is why I come to you!

So, is anyone willing to write the basic SQL query for the first or both of the queries above?

Thanks in advance for your help!
 

ProCom

Active member
#2
Ok, I paid to have this coded since I was in a hurry. Here are the basic queries:

---------THREADS PER NODE--------
SELECT n.title, t.threads, n.parent_node_id
FROM (
SELECT node_id, COUNT( node_id ) AS 'threads'
FROM xf_thread
GROUP BY node_id
)t, xf_node n
WHERE n.node_id = t.node_id



---------THREADS PER PARENT CATEGORY NODE DATE RANGE----------

SELECT n2.title, t3.total
FROM (
SELECT t2.parent_node_id, sum( t2.threads ) AS 'total'
FROM (
SELECT n.title, t.threads, n.parent_node_id
FROM (
SELECT node_id, COUNT( node_id ) AS 'threads'
FROM xf_thread
WHERE post_date < UNIX_TIMESTAMP( '2016-05-18' )
AND post_date > UNIX_TIMESTAMP( '2016-04-01' )
GROUP BY node_id
)t, xf_node n
WHERE n.node_id = t.node_id
)t2
GROUP BY t2.parent_node_id
)t3, xf_node n2
WHERE t3.parent_node_id = n2.node_id