Select to count amount per column

Question

Select to count amount per column

Navigation

#1 by (1 votes)

1

How do I make a select that returns the number of repeating devices, and the first and last date of these records?

Equipamento  |     Data     | Causa  |

   B            01/01/2017      x
   B            03/04/2017      Y
   A            05/01/2017      Y
   A            25/01/2017      x
   A            25/06/2017      Y
   C            01/05/2017      x
   C            01/06/2017      Y
   C            15/07/2017      x
   R            01/01/2017      x
   K            01/01/2017      x

.. in this example I would need it to return as below:

Equipamento  |    Quantidade  |   Data1      |  DAta2   

   B                  2          01/01/2017    03/04/2017   
   A                  3          05/01/2017    25/06/2017
   C                  3          01/05/2017    15/07/2017

I'm using Access

sql sql-select

asked by anonymous 10.01.2018 / 11:31

1 answer

Overlapping Divs Strange error in c ++ class

score 1 · Accepted Answer

You need to use Max and Min to get the maximum and minimum dates group the Equipment and use having to check which ones are more than 1 that will be repeated.

select Equipamento, count(*) as Quantidade, max(data) as Data1 , min(data) as Data2 
from SuaTabela_Equipamento 
group by Equipamento
having count(*) > 1