• 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

Well-known 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

Well-known 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