Title: SQL Help Required
Last modified: August 20, 2016

---

# SQL Help Required

 *  [indyparker](https://wordpress.org/support/users/indyparker/)
 * (@indyparker)
 * [13 years, 2 months ago](https://wordpress.org/support/topic/sql-help-required/)
 * I am using ELI’s Custom SQL Reports Creation plugin.
 * I want to produce the following SQL report (within WordPress):
 * Count all posts for all my Categories between two dates.
 * The output should be like this
 * Category Count
    Football 10 Cricket 5 Hockey 3
 * I have tried a few ideas but still cannot get it to work.
 * I can get all posts for one category i.e.
 *     ```
       SELECT DATE_FORMAT(post_date_gmt, '%M %Y') 'Date', post_title 'Post Title' , ID FROM wp_posts
       INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
       INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
       WHERE (wp_term_taxonomy.term_id = 134
          AND wp_term_taxonomy.taxonomy = 'category'
          AND wp_posts.post_type = 'post'
          AND wp_posts.post_status = 'publish')
       ORDER by wp_posts.post_date_gmt DESC;
       ```
   
 * Any help will be highly appreciated with this.
 * Thanks in advance.

Viewing 1 replies (of 1 total)

 *  [vtxyzzy](https://wordpress.org/support/users/vtxyzzy/)
 * (@vtxyzzy)
 * [13 years, 2 months ago](https://wordpress.org/support/topic/sql-help-required/#post-3626450)
 * I think this will get you started:
 *     ```
       $start_dt = "2010/07/01";
       $end_dt = "2012/12/31";
       $tax_ids = '32, 67, 81';  // your term_taxonomy_ids
       $sql = "
       SELECT t.name, count(1) as post_count
       FROM $wpdb->posts p
       INNER JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id)
       INNER JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
       INNER JOIN $wpdb->terms t ON (tt.term_id = t.term_id)
       WHERE tt.term_taxonomy_id IN ($tax_ids)
          AND tt.taxonomy = 'category'
          AND p.post_type = 'post'
          AND p.post_status = 'publish'
          AND DATE_FORMAT(p.post_date, '%Y/%m/%d') BETWEEN '$start_dt' AND '$end_dt'
       GROUP BY t.name
       ORDER by t.name ASC;
       ";
   
       $rows = $wpdb->get_results($sql);
   
       foreach ($rows as $row) {
          echo "$row->name ($row->post_count)<br />";
       }
       ```
   

Viewing 1 replies (of 1 total)

The topic ‘SQL Help Required’ is closed to new replies.

## Tags

 * [category](https://wordpress.org/support/topic-tag/category/)
 * [list](https://wordpress.org/support/topic-tag/list/)
 * [posts](https://wordpress.org/support/topic-tag/posts/)
 * [report](https://wordpress.org/support/topic-tag/report/)
 * [sql](https://wordpress.org/support/topic-tag/sql/)

 * In: [Fixing WordPress](https://wordpress.org/support/forum/how-to-and-troubleshooting/)
 * 1 reply
 * 2 participants
 * Last reply from: [vtxyzzy](https://wordpress.org/support/users/vtxyzzy/)
 * Last activity: [13 years, 2 months ago](https://wordpress.org/support/topic/sql-help-required/#post-3626450)
 * Status: not resolved

## Topics

### Topics with no replies

### Non-support topics

### Resolved topics

### Unresolved topics

### All topics