Select com group by

Question

I want to group the results that have the same name(varchar) and count the value(int) of the different key_id(varchar).

For example, if the records were those:

The results would be these (remembering that the value key_id is "random"):

Answer 1

The only way I know to do this exclusively with SQL would be something like this:

SELECT 
    CONCAT (
        'SELECT name, ',        
        GROUP_CONCAT(
            DISTINCT CONCAT(
                '(',
                    'SELECT IFNULL(SUM(value), 0) '
                    'FROM tabela b ',
                    'WHERE b.key_id = "', key_id, '"',
                    'AND b.name = a.name'
                ') ', key_id, ' '
            )
        ), ' '
        'FROM tabela a ',
        'GROUP BY name'
    ) INTO @sql
FROM tabela;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Note that there are 3 statements (the first generates a query, the second "prepares" the statement, and the third executes the query).

Although this works, I would only recommend using this if it is to generate some report, or for a very small database, never in an application, because for a few hundred key_ids, the BD would no longer swallow well.

The best solution would be to get all the data from the table and group according to what you need on the application side (using Node/PHP/C#/Java, whatever).

Answer 2

I suggest you try the following:

select sum(value) 
from nome_da_tabela 
group by name, key_id 

The 0-sum records will not be in the resultset, but before displaying the data in tabular format, fill the empty cells with 0 (zero).