Group table data with two columns

Question

Well, I am in a incognita that I would like to solve directly in the SQL query, see this supposed example:

TIPO | DE | IDENTIFICADOR
-------------------------
001  | 23 | 9
001  | 44 | 9
001  | 23 | 8
002  | 11 | 11

My wish is group the data in order to facilitate interpretation, in which case it should group together data from TYPE and IDENTIFIER, so that I know the OF to display to the user.

The return would be something like:

2 dados de 23, 24 sobre o identificador 9
1 dado de 23 sobre o identificador 8
1 dado de 11 sobre o identificador 11

what would be the means to accomplish this directly in the query, enabling a return similar to the example?

Answer 1

I did a part of your query, see it working here: http://sqlfiddle.com/#! 2/fe175e/4

CREATE TABLE `teste` (
  `tipo` varchar(10),
  `de`  varchar(10),
  `identificador` varchar (10)
);

INSERT INTO `teste` (`tipo`,`de`, `identificador`) VALUES
('001','23', '9'),
('001','44', '9'),
('001','23', '8'),
('002','11', '11');

SELECT identificador, GROUP_CONCAT(de ORDER BY de ASC SEPARATOR ',') AS campo
FROM teste
GROUP BY identificador

The part about counting how many there are of each I’ll leave for you to do ;-)

Answer 2

If you want to obtain as separate data:

SELECT COUNT(tipo) as quantidade,
       identificador,
       GROUP_CONCAT(de ORDER BY de SEPARATOR ', ') AS dados
  FROM tabela
 GROUP BY tipo,
          identificador

Or if you want to get it now with the proposed text:

SELECT CONCAT(COUNT(tipo), ' dados de ', GROUP_CONCAT(de ORDER BY de SEPARATOR ', '), ' sobre o identificador ', identificador) AS texto
  FROM tabela
 GROUP BY tipo,
          identificador

Where:

• COUNT account for the occurrences of a line based on the grouping function (GROUP BY);
• CONCAT concatenates the fields listed (includes the text between ');
• GROUP_CONCAT concatenates the columns lists based on the grouping function.

COUNT

Returns a Count of the number of non-NULL values of expr in the Rows retrieved by a SELECT statement. The result is a BIGINT value.

In free translation:

Returns the numerical count of non-NON-NORS values of the expression in the row selected by the clause SELECT. The result is a value BIGINT.

---

CONCAT

Returns the string that Results from concatenating the Arguments.

In free translation:

Returns the string resulting from concatenating the arguments.

---

GROUP_CONCAT

This Function Returns a string result with the Concatenated non-NULL values from a group.

In free translation:

This function returns the string resulting from the concatenation of non-NON-NORS values of a group.