[SOLVED] SQL query and display only date

Issue

CREATE TABLE `celula` (
  `ID_Celula` int(255) unsigned NOT NULL AUTO_INCREMENT,
  `integrantes` text,
  PRIMARY KEY (`ID_Celula`)
);

INSERT INTO `celula` (`ID_Celula`, `integrantes`) VALUES
(1, '2:2014-08-13,4:2014-08-13,6:2014-08-13,7:2014-08-13'),
(2, '3:2014-08-13,5:2014-08-13,6:2014-08-13');

How do I show only the date of the column members?

online test http://sqlfiddle.com/#!2/1729fd/4

Solution

You can do something like this

SELECT YEAR(date) year, MONTH(date) month, COUNT(*) count
  FROM
(
  SELECT SUBSTRING_INDEX(value, ':', 1) id, 
         CAST(SUBSTRING_INDEX(value, ':', -1) AS DATE) date
    FROM
  (
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(integrantes, ',', n), ',', -1) value
      FROM celula CROSS JOIN 
    (
      SELECT a.N + b.N * 10 + 1 n
      FROM 
      (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
     ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ) tally
     WHERE n <= 1 + (LENGTH(integrantes) - LENGTH(REPLACE(integrantes, ',', '')))
  ) q
) o
 GROUP BY YEAR(date), MONTH(date)

Output:

| YEAR | MONTH | COUNT |
|------|-------|-------|
| 2014 |     8 |     7 |

Here is SQLFiddle demo

If you need to split more than 100 values you can easily tweak the inner most SELECT to produce more rows, or you can even simplify things you make it a persistent (tally) table in your database.

Answered By – peterm

Answer Checked By – David Goodson (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.