Problems with group concat SQL

Question

Problems with group concat SQL

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

From my previous question and with the solution that gave me the sentence worked correctly.

This was the correct sentence

SELECT valor, 
  group_concat(DISTINCT IF(dia=1, valor_b, NULL)) dia_1,
  group_concat(DISTINCT IF(dia=2, valor_b, NULL)) dia_2,
  group_concat(DISTINCT IF(dia=3, valor_b, NULL)) dia_3,
  group_concat(DISTINCT IF(dia=4, valor_b, NULL)) dia_4,
  group_concat(DISTINCT IF(dia=5, valor_b, NULL)) dia_5
FROM test
GROUP BY valor;

In this case, the problem is as follows. Instead of showing valor_b I have to count how many times valor_b appears per day.

To do this, simply change the part of the code in which I say that it shows valor_b :

 SELECT valor, 
      group_concat(DISTINCT IF(dia=1, count(valor_b), NULL)) dia_1,
      group_concat(DISTINCT IF(dia=2, count(valor_b), NULL)) dia_2,
      group_concat(DISTINCT IF(dia=3, count(valor_b), NULL)) dia_3,
      group_concat(DISTINCT IF(dia=4, count(valor_b), NULL)) dia_4,
      group_concat(DISTINCT IF(dia=5, count(valor_b), NULL)) dia_5
    FROM test
    GROUP BY valor;

It returns the following error:

1111 - Invalid use of group function

This would be a valid data structure for the test table

dia valor valor_b
=== ===== =======
1    1     b1
1    2     b2
1    1     b3
1    2     b3
2    1     b2
2    3     b1
2    1     b1
2    1     b1
2    1     b2
1    3     b3
5    1     b4
5    3     b1

sql phpmyadmin

asked by Lombarda Arda 09.05.2017 в 09:55

source

2 answers

1

If you are interested in adding up how many times the value b appears, what if you try the function CASE when instead of if ?

Something like that, taking into account the cases in which you want to add depending on the value_b:

select  
case when valor_b=b1 then sum(dia) else 
case when valor_b=b2 then ...

answered by 09.05.2017 в 11:03

Error compiling typescript with visual studio code C # Access to private methods

score 2 · Accepted Answer

I've done the same question with my another account on the site in English.

The correct sql is as follows,

SELECT valor, 
      sum(IF(dia=1, 1, NULL)) dia_1,
      sum(IF(dia=2, 1, NULL)) dia_2,
      sum(IF(dia=3, 1, NULL)) dia_3,
      sum(IF(dia=4, 1, NULL)) dia_4,
      sum(IF(dia=5, 1, NULL)) dia_5
    FROM test
    GROUP BY valor;

Each time a day appears, it is added up and results in the'count (value_b) '