Aggregate and sort WordPress data with MySQL GROUP BY and ORDER BY

Home » Coding » Aggregate and sort WordPress data with MySQL GROUP BY and ORDER BY
July 25, 2021 Coding, MySQL
Aggregate and sort WordPress data with MySQL GROUP BY and ORDER BY

Two of the most powerful tools in any SQL developer’s toolkit are GROUP BY and ORDER BY — yet they are frequently misunderstood or misused, especially by developers who learned SQL by copying snippets rather than studying the query execution model. GROUP BY collapses multiple rows that share a common value into a single summary row, and you use aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() to compute values across those grouped rows. ORDER BY simply sorts the result set — it has nothing to do with grouping. A common mistake is assuming that GROUP BY implies a particular sort order; it does not, and MySQL’s behaviour without an explicit ORDER BY is undefined. The HAVING clause filters groups after aggregation, where WHERE filters rows before aggregation — knowing the difference prevents incorrect queries that silently return wrong data. These techniques are immediately applicable to the WordPress database: you can count posts per category, sum WooCommerce order totals per month, or find the most-commented posts with a single query. Combine this knowledge with the EXPLAIN tool to ensure your aggregation queries use indexes, and with the find-and-replace guide for bulk data operations. The examples below use the WordPress tables directly so you can run them in phpMyAdmin immediately.

Problem: You need to count, sum, or group WordPress database records by category, date, or author, and return results in a specific order.

Solution: Run the following queries in phpMyAdmin or the MySQL CLI:

-- Count published posts per post author
SELECT post_author,
       COUNT(*) AS post_count
FROM ynYYAa7bFT_posts
WHERE post_status = 'publish'
  AND post_type   = 'post'
GROUP BY post_author
ORDER BY post_count DESC;

-- Count posts per category
SELECT t.name          AS category,
       COUNT(tr.object_id) AS post_count
FROM ynYYAa7bFT_terms         t
JOIN ynYYAa7bFT_term_taxonomy  tt ON tt.term_id          = t.term_id
JOIN ynYYAa7bFT_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN ynYYAa7bFT_posts           p  ON p.ID               = tr.object_id
WHERE tt.taxonomy   = 'category'
  AND p.post_status = 'publish'
  AND p.post_type   = 'post'
GROUP BY t.term_id
ORDER BY post_count DESC;

-- Published posts per month (last 12 months)
SELECT DATE_FORMAT(post_date, '%Y-%m') AS month,
       COUNT(*)                        AS post_count
FROM ynYYAa7bFT_posts
WHERE post_status = 'publish'
  AND post_type   = 'post'
  AND post_date  >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY month
ORDER BY month ASC;

-- HAVING example: only categories with more than 5 posts
SELECT t.name AS category,
       COUNT(tr.object_id) AS post_count
FROM ynYYAa7bFT_terms t
JOIN ynYYAa7bFT_term_taxonomy tt  ON tt.term_id = t.term_id
JOIN ynYYAa7bFT_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy = 'category'
GROUP BY t.term_id
HAVING post_count > 5
ORDER BY post_count DESC;

NOTE: In MySQL 5.7+ with sql_mode=ONLY_FULL_GROUP_BY (the default), every column in the SELECT list must either appear in the GROUP BY clause or be wrapped in an aggregate function. If you receive a “Expression #X of SELECT list is not in GROUP BY clause and contains nonaggregated column” error, add the offending column to GROUP BY or wrap it in ANY_VALUE(). Running ANALYZE TABLE periodically keeps the optimizer statistics fresh and ensures your aggregation queries choose the most efficient execution plan.

Related posts