Group elements from mysql query

[Champak]

I want to group elements from a mysql query, but the way I'm thinking of going about it seems inefficient and I'm checking if there is a better way.

I'm querying a table column with skus that have one letter, 1 to 3 digits, a dash (which we'll call the prefix), and then 7 digits. So it can look like A3-0000000, C12-2323323 or J143-7829312. I want to get the count of each of the prefixes (A3, C12, J143, etc.). Let's assume I don't know the prefixes or how many different ones there are.

My thinking on how to do this is:

1/ Query the database and sort all the sku.  (before someone says I should do this all in the query, I have no idea how to do that if it's possible)

2/ loop through the array with a stringsplit to get just the prefixes.

3/ remove the duplicates (this will now be my prefix array).

4/ loop through the original array with the prefix array with an _arraysearch to get the count of the skus with each prefix.

5/ put that count next to the prefix in the prefix array (multi-d array).

Is this the best way to go about this?

[jchd]

A better way? Yes: SQL itself!

Given a table like this:

CREATE TABLE "SKUs"("SKU" CHAR);

loaded with sample data:

SKU
A3-0000000
C12-2323323
J143-7829312
B54-5487451
A3-4875962
C12-1234567
C12-7654321
F40-7894560
A3-7894562
A3-4561238

just run the following query:

select SUBSTR (sku, 1, INSTR (sku, '-') - 1) "Prefix", count(*) "Counter" from skus group by prefix order by prefix;

and you get this:

Prefix    count(*)
A3    4
B54    1
C12    3
F40    1
J143    1

I used SQLite string functions, I've f*ckall idea how they translate into MySQL dialect.

Edited August 30, 2023 by jchd

[Danp2]

From here --

select substring_index(sku,'-',1) prefix, count(*) from yourtable

 

[Champak]

Thanks both of you.

This worked.

"SELECT substring_index(`sku`,'-',1) 'Prefix', count(*) 'Counter' from `roottesCart_product` where quantity = 1 group by `Prefix` order by `Prefix`;"