How to turn a query row into columns and group according to the first 4 characters?

Question

I would like to transform the return of my SQL query into an array where I select the first four characters, which will act as a kind of group, and return the values for that group in columns. I’m analyzing the PHP language.

As I would like

Answer 1

In the clasp GROUP BY use the function left() which returns the N characters left of the string, as a grouping criterion.

SELECT * FROM tabela GROUP BY LEFT(PRODUCT, 4)

Functional example - sqlfiddle

create table t (
   id int(11) primary key auto_increment,
   product varchar(50),
   value int(11)
);

insert into t values
(null, 'A00102', 50),
(null, 'A00103', 20),
(null, 'A00104', 60),
(null, 'B00101', 80),
(null, 'B00102', 10),
(null, 'C00101', 22);

Consultation:

SELECT left(product, 4) as grupo, sum(`value`) as total FROM t GROUP BY left(product, 4)

Return:

grupo|total
A001 |130
B001 |90
C001 |22