I would like to get the count of occurrences of a given list of values in a column using a single SQL query. The operations must be optimised for performance.

Please refer the example given below,

Sample Table name - history

Expected Output

Need to get the count of occurrences for these given list of codes 627czl, 1lqnd8, esdop9 in the format given below.

Method I tried in show below but the count of each input is shown as a new column using this query,

SELECT 
    sum(case when h.code_list = 'esdop9' then 1 else 0 end) AS count_esdop9,
    sum(case when h.code_list = '627czl' then 1 else 0 end) AS count_627czl,
    sum(case when h.code_list = '1lqnd8' then 1 else 0 end) AS count_1lqnd8,
FROM history h;

Note - The number inputs need to be given in the query in 10 also the real table has millions of records.

CodePudding user response：

If i properly understand you need to get the count of occurrences for the following codes: 627czl, 1lqnd8, esdop9.

In this case you can try this one:

SELECT code_list, count(*) as count_
  FROM history
 WHERE code_list in ('627czl','1lqnd8','esdop9')
 GROUP BY code_list
 ORDER BY count_ DESC;

dbfiddle

If you need to get the count of occurrences for all codes you can run the following query:

SELECT code_list, count(*) as count_
  FROM history
 GROUP BY code_list
 ORDER BY count_ DESC;

CodePudding user response：

you can try to use GROUP BY Something like this

SELECT code_list, COUNT(1) as 'total' ROM h GROUP by code_list order by 'total' ;