SQL Query divide a field

Question

database example

I'd like, for each commercial, identified here by a code, to get the number of bills (in French: facture) being associated with him.

For example, here, with this part of the database, I'd like to have something like that: R08: 22; R087: 19; R11 : 3; R00 : 3; R062: 1; R026: 1;

A commercial code starts with a 'R' followed by a few digits and if the bill is associated with more than one commercial, a '|' separates the codes. By the way, most of the bills are associated with only one commercial.

My problem is to do that only with SQL. if it's too complicated, I'll do it with PHP.

I hope my question is understandable and that you'll know how to help me.

An other example of data:

R015        15040205
R012        14250123
R012|R038   14250123
R015|R012   14250123
R005        14250123

I'd like a query that returns:

R005        1
R038        1
R015        2
R012        3

actually, I would like a query that counts the number of occurrences of each code. The second data doesn't matter

Some sample data and the expected result would be great. (As formatted text, not images.) — jarlh, Dec 06 '18 at 07:50
How can i write a part of my database? i can't make an array — tom marchand, Dec 06 '18 at 08:02
What do you mean? Edit the question here, or writing in the actual database? — jarlh, Dec 06 '18 at 08:03
GROUP BY and count() is the way to go. The problem is those pipe separated values... — jarlh, Dec 06 '18 at 08:45
Yep, given your data structure where two 'rows' are compressed into one using a | you will probably need to do this in php unless your DBMS has something to deal with this, pretty sure MySQL can't do it. — AntG, Dec 06 '18 at 09:12
MySQL cant do it natively, but you can still achieve it in SQL if you create a user defined function that mimick the `split()` function (aka `explode()` in php) . Check this https://stackoverflow.com/questions/2696884/split-value-from-one-field-to-two. On a side note, this is a purely database design issue. So instead of creating a script for your counts, you would better to create a script that will inject the values in a many-to-many table, in order to fix your data once for all. After that all your issues will be solved. — Thomas G, Dec 06 '18 at 10:49

score 1 · Accepted Answer · answered Dec 06 '18 at 12:24

1

If you have at most two codes (as in your sample data), then you can use substring_index():

select code, count(*)
from ((select substring_index(code_commercial, '|', 1) as code
       from t
      ) union all
      (select substring_index(code_commercial, '|', -1)
       from t
       where code_commercial like '%|%'
      )
     ) c
group by code;

The more important point is that your data model is really, really, really bad. You should be fixing it. SQL is not designed to store multiple values in a single column -- or to use strings to store this type of data.

You want a junction table, with separate rows for each code. Then your query would be easier to write and run faster.

answered Dec 06 '18 at 12:24

Gordon Linoff

1,242,037
58
646
786

The thing is that this table is a list of bills made by one or more commercial. If I separate in multiply rows I won't be able to say if it's the same bill they made together or two different bils created the same time – tom marchand Dec 06 '18 at 13:09
@tommarchand . . . Sounds like you want a `bills` table with one row per bill and a separate table for `billCodes`. – Gordon Linoff Dec 06 '18 at 13:19
anyway, I think I know how I'll do, thanks for the answer it works fine – tom marchand Dec 06 '18 at 13:22

SQL Query divide a field

1 Answers1