help with group by in mysql

Question

help with group by in mysql

Navigation

#1 by (2 votes)

1

I have to take out the total amount for each unit that I have in my table, the problem is that a record that supposedly belongs to unit 3 belongs to unit 1, I do not know how to group it so that it is correct, this is my query.

select SUM(TOTAL) as total, SUBSTRING(UNIDAD,1,1) as unidad from estruc_programatica group by unidad;

I can only get it from the third table, which I show in the image but I want the table in the middle, the record that I have to group with unit 1 is 3180, it would be {1100,1200, 3180} unit 1 , {2300} unit 2 and {3900, 3050} unit three. I've been trying to get it out for a while, but I can not.

mysql sql group-by

asked by Karla Huerta 21.06.2018 в 19:38

source

1 answer

html table with Json Problems obtaining data when converting an int to a decimal field.

score 2 · Accepted Answer

According to what you indicate, you want to group by the first character of unidad except the unidad == 3180 that would correspond to the unit 1 . You should do something like this:

select  SUM(TOTAL) as total, 
        CASE WHEN UNIDAD = 3180 THEN 1 ELSE SUBSTRING(UNIDAD,1,1) END as unidad 
        from estruc_programatica 
        group by CASE WHEN UNIDAD = 3180 THEN 1 ELSE SUBSTRING(UNIDAD,1,1) END;

Clarification: If you do not have it, it is advisable to have a secondary table that makes the relationship between the original units and the "unit" you are looking for, in this way you could more elegantly handle these exceptions, for example: (3180, 1)