Looking for another method to count in a single query

Question

So, I found this article is very useful Join two tables and return data and count in a single query and here Single query to return counts over different IDs in a single record for some IDs or parameter to use.

So my question is, if I have more than 10 IDs or parameters for which I need counts, using COUNT(CASE WHEN ... (as below) becomes awkward and hard to maintain:

SELECT COUNT(CASE WHEN table1.id = 1 THEN table2.id END) as count1,
       COUNT(CASE WHEN table1.id = 2 THEN table2.id END) as count2,
       COUNT(CASE WHEN table1.id = 3 THEN table2.id END) as count3,
       ...,
       COUNT(CASE WHEN table1.id = 10 THEN table2.id END) as count10

Is there a better way to solve this problem?

More details: I am using MySql database and use Codeigniter to process my data. This my current query:

<?php defined('BASEPATH') OR exit('No direct script access allowed');
class Model_provenance extends CI_Model {
    function __contruct()
    {
        parent :: __contruct();
        $this->load->database();
    }
    function count_koleksi_by_provenance_status() {
        $query = $this->db->select('provenance.provenance_status, cat_provenance.status AS provenance_status,
                                    COUNT(provenance.provenance_status) AS total,
                                    COUNT(CASE WHEN provenance.provenance_status = 1 then cat_provenance.status END) AS count1,
                                    COUNT(CASE WHEN provenance.provenance_status = 2 then cat_provenance.status END) AS count2,
                                    COUNT(CASE WHEN provenance.provenance_status = 3 then cat_provenance.status END) AS count3,
                                    COUNT(CASE WHEN provenance.provenance_status = 4 then cat_provenance.status END) AS count4,
                                    COUNT(CASE WHEN provenance.provenance_status = 5 then cat_provenance.status END) AS count5,
                                    COUNT(CASE WHEN provenance.provenance_status = 6 then cat_provenance.status END) AS count6,
                                    COUNT(CASE WHEN provenance.provenance_status = 7 then cat_provenance.status END) AS count7,
                                    COUNT(CASE WHEN provenance.provenance_status = 8 then cat_provenance.status END) AS count8,
                                    COUNT(CASE WHEN provenance.provenance_status = 9 then cat_provenance.status END) AS count9', FALSE)
                        ->join('provenance', 'provenance.provenance_status = cat_provenance.ID', 'LEFT')
                        ->group_by('cat_provenance.status')
                        ->order_by('cat_provenance.ID', 'ASC')
                        ->from('cat_provenance')
                        ->get();
        if($query->num_rows() > 0) {
            return $query->result();
        } else {
            return NULL;
        }
    }
}

The cat_provenance data table looks like this:

ID | status
------------------
1  | Grant
2  | Legacy
3  | Gift
4  | Purchase
5  | Recompense
6  | Discovery
7  | Search
8  | Exchange
9  | Conversion

The provenance data table has the following columns:

• ID
• provenanceID
• coll_regID
• authorID
• provenance_status
• date_from
• date_to
• buy_price
• provenance_desc
• post_by
• post_create
• edit_by
• edit_datetime

I want the final result to look something like this:

• Grant (2)
• Legacy (5)
• Gift (0)
• Purchase (0)
• Recompense (1)
• Discovery (10)
• Search (5)
• Exchange (3)
• Conversion (0)

The key here is I need to see a total for every category, even if that total is 0.

So if I have 20 ID or more in the cat_provenance table, do I have to write this part:

COUNT(CASE WHEN provenance.provenance_status = 1 then cat_provenance.status END) AS count1

or is there another method that I can use so I do not write that part one by one?

Hopefully, this better explains my question.

Answer 1

From the information provided, it sounds like you don't absolutely have to have the data in the format:

  Grant | Legacy | Gift | Purchase | ...
 -------+--------+------+----------+------
    2   |    5   |   0  |     0    | ...

If you can use the data in the format:

  Status  | Count
----------+-------
 Grant    |     2
 Legacy   |     5
 Gift     |     0
 Purchase |     0
 ...      |   ...

Then the simplest solution is to pull the data back as follows:

SELECT cat.status as `Status`, COUNT(p.id) as `Count`
  FROM cat_provenance cat
         LEFT JOIN provenance p ON (cat.ID = p.provenance_status)
 GROUP BY cat.status WITH ROLLUP

Results will look like the above. By including WITH ROLLUP, we include a row with Status NULL and Count the grand total for all categories.

  Status  | Count
----------+-------
 ...      |   ...
 NULL     |    26

Note: code untested