CODEWITHSUNDEEP
mysqlsql
tom marchand
tom marchand

Reputation: 57

SQL Query divide a field

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

Upvotes: 0

Views: 80

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 1

Related Questions