Mysql – Looking for another method to count in a single query

casecountMySQL

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.

Best Answer

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

Related Solutions

MySQL Ticket Counting – Counting Number of Open Tickets Per Day in MySQL

You can use conditional sum for this calculation something as

select 
date_format(created,'%d-%m') as created_date , 
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;

Here is a test case

mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id   | created             | resolved            | status   |
+------+---------------------+---------------------+----------+
|    1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open     |
|    2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new      |
|    3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
|    4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open     |
|    5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new      |
|    6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select 
    -> date_format(created,'%d-%m') as created_date , 
    -> sum( case when status='open' then 1 else 0 end )+
    -> sum( case when status='new' then 1 else 0 end )+
    -> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
    -> from test
    -> where
    -> YEAR(Created) = '2015'
    -> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    2 |
+--------------+------+
2 rows in set (0.00 sec)

UPDATE The count of open is a incremental as per the comment. So the following should do the trick

select
created_date,
@open:= @open+open_ticket-resolved as open
from(
 select 
 date_format(created,'%d-%m') as created_date , 
 sum( case when status='open' then 1 else 0 end )+
 sum( case when status='new' then 1 else 0 end )+
 sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
 from test
 where
 YEAR(Created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

From the above sample data it will have

 +--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
+--------------+------+

UPDATE:

Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join something as

select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
 select 
 date_format(t.created,'%d-%m') as created_date , 
 sum( case when t.status='open' then 1 else 0 end )+
 sum( case when t.status='new' then 1 else 0 end )+
 sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
 coalesce(tot,0) as resolved_ticket
 from test t left join(
    select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
 )x on date(x.resolved) = date(t.created) 
 where
 YEAR(t.created) = '2015'
 group by created_date
)x,(select @open:=0)r order by created_date ;

OUTPUT :

+--------------+------+
| created_date | open |
+--------------+------+
| 10-05        |    3 |
| 11-05        |    5 |
| 12-05        |    7 |
+--------------+------+

Mysql – How to improve query count execution with theSql replicate

Give this a try:

Add this composite index to user_activity: INDEX(user_id, action). Then

SELECT SUM(x.ct), u.user_type, x.action
    FROM (
        SELECT user_id, action, COUNT(*) AS ct
            FROM user_activity
            GROUP BY user_id, action
         ) x
    JOIN user u  ON x.user_id = u.id
    GROUP BY x.action, u.user_type;

Best Answer

Related Solutions

MySQL Ticket Counting – Counting Number of Open Tickets Per Day in MySQL

Mysql – How to improve query count execution with theSql replicate

Related Question