Simulate a kind of FOR EACH in Mysql

Question

Simulate a kind of FOR EACH in Mysql

Navigation

#1 by (1 votes)

1

Currently I'm doing a query that shows the total of reports of a specific type sent (sent = 1) for each user. The current query that I have works and is:

SELECT username,

SUM(inf_name='INF_01') AS INF_01, 
SUM(inf_name='INF_02') AS INF_02, 
SUM(inf_name='INF_03') AS INF_03, 
SUM(inf_name='INF_04') AS INF_04, 
SUM(inf_name='INF_05') AS INF_05

FROM  'informes' 
WHERE enviado=1
GROUP BY username

My question is if there is a way to make those SUM () ... so that when you enter a new type of report (eg INF_06) you should not edit the query, simulate a kind of FOR EACH that allows me to have the total amount for each type of report and show it as a new column for the query.

Greetings.

mysql sql

asked by Eles. 14.09.2017 в 21:26

source

1 answer

each letter is in lower case and uppercase successively How to know if an insert was made in Mysql database

score 1 · Accepted Answer

One possible way is to build a dynamic query, let's see this proof of concept:

create table if not exists informes(
    username varchar(255),
    inf_name varchar(255)

);

insert into informes(username, inf_name) values ( 'juan',  'INF_01');
insert into informes(username, inf_name) values ( 'pedro', 'INF_01');
insert into informes(username, inf_name) values ( 'juan',  'INF_02');
insert into informes(username, inf_name) values ( 'luis',  'INF_03');

SET @sumas = "";
select  GROUP_CONCAT(CONCAT("sum(inf_name='", inf_name, "') AS ", inf_name) SEPARATOR ', ')
        INTO @sumas
        from ( select inf_name
                      from informes
                      group by inf_name
              ) G;

SET @query = CONCAT("select username, ", @sumas, " FROM informes GROUP BY username; ");
select @query;

If we review the variable @query we see how we have the query:

select username, sum(inf_name='INF_01') AS INF_01, sum(inf_name='INF_02') AS INF_02, sum(inf_name='INF_03') AS INF_03 FROM informes GROUP BY username;

We can add line breaks to make it look better, but beyond that, it's basically what you were looking for, but built dynamically, the only thing that remains is to execute it, like this:

PREPARE stmt1 FROM @query; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;

And we will obtain the desired output:

username INF_01 INF_02 INF_03
======== ====== ====== ======
juan     1      1      0
luis     0      0      1
pedro    1      0      0

Of course, all dynamic queries have limits:

Variable size @query
Number of columns that a query can return

I do not know about MySql, I suggest you investigate them.