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

Navigation
3

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

HowI'dLike

    
asked by anonymous 31.10.2017 / 18:10

1 answer

4

In class GROUP BY use the function left() which returns the N characters to the left of the string, as the grouping criteria. 

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

Functional sample - 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);

Inquiry: 

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
    
31.10.2017 / 18:21
How to delete a term entered a form using jQuery? Remove space between menu and content