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.

• McNets: Please tag your RDBMS and add more information like table schema, some sample data and the desired result.
• Vérace: I'm voting to close this question as off-topic because I agree with @McNets - you need to provide more information (in the question!).
• RDFozz: There's still one inconsistency in your question. Currently, it appears you're trying to retrieve the counts all in the same row, with the categories as the column headers, and the counts as the row values.. Your desired output appears to be one row per category, with the category name and the count as the two columns. The answer is notably different for all in one row from one row per category. Please clarify.
• Sigit D Red Hat: @RDFozz Something like that is what I want. If you do not use the above method, then the category name and the amount that is not in the provenance table will not appear in the view because there is no data. So I want all categories in the cat_provenance table to display the category name and the number even if there is no data in the provenance table.

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

• Sigit D Red Hat: Thank you for your answers. I have succeeded with the code I mentioned above. But the problem is if I have so many 'status' categories in the 'cat_provenance' table, I have to write "COUNT (CASE WHEN ..." repeatedly until the last ID.
• RDFozz: With the code above, you don't need the COUNT (CASE WHEN... code; this code is instead of that code.