How do I modify this sql do display a count by node type?

Here’s the report I’m currently getting:

+-----------+----------------+
| new nodes | month          |
+-----------+----------------+
|        21 | May 2000       |
|        15 | June 2000      |
|        11 | July 2000      |
|        10 | August 2000    |
|        10 | September 2000 |

Here’s the SQL behind the report:

SELECT COUNT(*) AS 'new nodes', FROM_UNIXTIME(created, '%M %Y') AS month
FROM node
WHERE status = 1 AND type IN ('sc_forum', 'article', 'blog', 'download', 'event', 'video', 'idea', 'poll', 'issue', 'page')
GROUP BY FROM_UNIXTIME(created, '%Y %m');

I would love to be able to further bread down the number of nodes by the “type”. Maybe something like this:

+-----------+----------------+
| type    | new nodes | month    |
+-----------+----------------+
| article  |        2 | May 2000 |
| blog     |        8 | May 2000 |
| download |       11 | May 2000 |
| article  |        6 | Jun 2000 |
| blog     |        12 | Jun 2000 |
| download |       01 | Jun 2000 |

add type to both the SELECT and GROUP BY clauses

2 Likes

Thanks r937!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.