SQL limiting Count function based on IN clause using comma delimited list

Question

I have a table of Matricies (JobMatricies) ID, Desc, DeptIDs

1   Admin (PM)  6,7,138,131,11,9,10,134,135,14,105,129
5   Sales Processing (PM)   92,16,153,17,91,32,26,93,99,18,89,90,155,19
6   Construction Processing (PM)    100,36,20,136,22,88,23,25,34,106,38,39,132,41,42,43,154,152,84

DeptIDs are a Comma Delimited list of departments that I want to use to count how many records are represented by the Matrix.

Normally I would do something like....

select  Matrix_ID, 
        Matrix_Desc, 
        JobCount =  (select count(sched_ID) from JobSchedule where dept_ID in                                                    (**92,16,153,17,91,32,26,93,99,18,89,90,155,19**))
from    jobMatrices

How do I replace the hard coded delimited string with the ID's stored with each matrix, so that I can produce a list of matricies with their own unique count based on the comma delimited string that is stored with each matrix.

Thanks

Fix your data model to be a proper relational model, where you do not store multiple numeric ids in a single string column. I'm sure that will simplify most queries you want to write, including this one. — Gordon Linoff, Jul 08 '16 at 16:31
Thanks Gordon, appreciate the comments. Yes, we can add another table to store each ID as a separate record then use a sub select. Is there a way to solve the issue with how the data is currently stored? — Scott Terrell, Jul 08 '16 at 16:42
What is your RDBMs ? Different flavors has different ways to handle comma separated values. — Jorge Campos, Jul 08 '16 at 16:45
In Postgres you can use `cardinality(string_to_array(deptids,','))` — , Jul 08 '16 at 17:44
You will probably find an implementation of a `split` function appropriate for your database if you search for that. — shawnt00, Jul 08 '16 at 18:13

score 0 · Answer 1 · edited May 23 '17 at 10:28

0

I just answered a similar question where the poster wanted to sum the delimited list of numbers in a similar table layout. My solution used CTE's in Oracle to turn that list into a CTE table which would allow you to join against it. I believe that technique would be of use here if your RDBMS supports that. Please have a look: https://stackoverflow.com/a/38231838/2543416

edited May 23 '17 at 10:28

Community

1
1

answered Jul 08 '16 at 17:39

Gary_W

9,933
1
22
40

score 0 · Answer 2 · edited Jul 08 '16 at 17:52

0

select s.ID
     , m."Desc"
  from JobSchedule s
     , jobMatrices m
 where position(',' || s.ID || ',' in ',' || m.DeptIDs || ',')>0;

select m."Desc"
     , count(s.ID) JobCount
  from JobSchedule s
     , jobMatrices m
 where position(',' || s.ID || ',' in ',' || m.DeptIDs || ',')>0
 group by m."Desc";

edited Jul 08 '16 at 17:52

Jorge Campos

22,647
7
56
87

answered Jul 08 '16 at 17:49

Viktor Wellboy

21
2

Hi there, welcome to SO, next time take your time to Format your codes in your future answers. Just adding a blank line before the text and four white spaces before every line of code. – Jorge Campos Jul 08 '16 at 17:53

score 0 · Answer 3 · answered Jul 08 '16 at 18:00

In MySQL, you can treat a comma-separated string as a SET, and there's a builtin function FIND_IN_SET() that helps:

select  m.Matrix_ID, 
        m.Matrix_Desc, 
        (select count(sched_ID) from JobSchedule 
         where FIND_IN_SET(dept_ID, m.DeptIds)) AS JobCount
from    jobMatrices AS m;

This will have terrible performance, however.

If you use some RDBMS other than MySQL, they may have a different solution that works similarly.

You should be specific in your question and tag your question appropriately. You only tagged your question sql, but this is a language used by many RDBMS vendors.

SQL limiting Count function based on IN clause using comma delimited list

3 Answers3