Select com group by

Asked

Viewed 225 times

1

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:

registros

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

inserir a descrição da imagem aqui

2 answers

0

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).

-1

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).

  • Could improve your response using formatting see How to format my posts using Markdown or HTML?.

  • This results in multiple results with the same "Name".

  • Certainly. But this is the correct result using only SQL. You must then work the resultset to present the data without repeating the "name". It is good practice to keep the presentation layer source code separate.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.