Count record of two date fields grouping by year

Asked

Viewed 401 times

1

I need to get the sum of records of two fields from the same table but grouping the year to assemble an Areachart chart

--

-- Table structure cadastros

CREATE TABLE IF NOT EXISTS `cadastros` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`inicio_contrato` date NOT NULL,
`fim_contrato` date NOT NULL,
`datacad` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`ativo` enum('0','1') NOT NULL DEFAULT '1',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--

-- Extracting data from the table cadastros

INSERT INTO `cadastros` (`ID`, `inicio_contrato`, `fim_contrato`, `datacad`, `ativo`) 
VALUES
(1, '2015-01-01', '2015-12-31', '2015-01-15 05:16:53', '0'),
(2, '2015-01-01', '2015-12-31', '2015-09-15 07:18:08', '0'),
(3, '2015-01-01', '2015-12-31', '2015-09-15 07:54:07', '0'),
(4, '2015-01-01', '0000-00-00', '2015-09-15 08:31:45', '1'),
(5, '2016-01-01', '0000-00-00', '2016-09-15 04:17:12', '1'),
(6, '2016-01-01', '2016-12-31', '2016-09-15 06:18:32', '0'),
(7, '2016-01-01', '2016-12-31', '2016-09-15 06:18:08', '0'),
(8, '2017-01-01', '2017-03-31', '2017-09-15 08:12:43', '0'),
(9, '2017-01-26', '0000-00-00', '2017-01-26 07:38:36', '1'),
(10, '2017-03-31', '0000-00-00', '2017-03-31 08:44:13', '1');

I need you to print me the result below:

['ANO', 'INICIO', 'FIM'],
['2015', '4', '3'],
['2016', '3', '2'],
['2017', '3', '1']

In my attempt I managed to count a field but still not able to count both in the same query:

SELECT YEAR(inicio_contrato) AS anos,
COUNT(YEAR(inicio_contrato)) AS total
FROM cadastros 
GROUP BY anos 
ORDER BY anos DESC 
LIMIT 10

This way I can group by YEAR and count the BEGINNING

php mysql

  • Hello, please add the example records you have in the table. Ideal that there are not many, but a few enough to characterize the problem/situation.

  • Which fields you want to add specifically ?

  • You need to COUNT how many records started each year using the column inicio_contrato and COUNT how many ended up using the column fim_contrato when it has value (date)

1 answer

3


As you are grouping by different columns, you need to treat the darlings separately.

We can start from the query simple, based on its:

    SELECT
      YEAR( inicio_contrato ) AS ano,
      COUNT( * ) AS contagem
    FROM
      cadastros
    GROUP BY
      YEAR( inicio_contrato )
    ORDER BY
      inicio_contrato

And then apply the same logic to the end, using a JOIN to show the two related returns:

SELECT
   inicio.ano,
   inicio.contagem,
   fim.contagem
FROM (
      --- aqui vai a query inicial ---
      SELECT
         YEAR( inicio_contrato ) AS ano,
         COUNT( * ) AS contagem
      FROM
         cadastros
      GROUP BY
         YEAR( inicio_contrato )
      ORDER BY
         inicio_contrato

   ) inicio JOIN (
      --- mesma lógica, para o fim ----
      SELECT
         YEAR( fim_contrato ) AS ano,
         COUNT( * ) AS contagem
      FROM
         cadastros
      GROUP BY
         YEAR( fim_contrato )
      ORDER BY
         fim_contrato

   ) fim ON inicio.ano = fim.ano
ORDER BY
   inicio.ano;

See working on SQL Fiddle.

Browser other questions tagged php mysql

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