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.