Select to count quantity per column

Asked

Viewed 2,907 times

1

How I make a select that returns the amount of equipment repeating, 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 you 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 am using Access

sql select-sql
  • 1

    select Count(*), equipment, max(date) as data1 , min(date) as data2 from table group by equipment

  • The above friend is correct. An example: http://sqlfiddle.com/#! 17/d61ef/4

  • @Diegoschmidt K and R are not repeating equipment ... missing Having Count(*) > 1

  • @Marconciliosouza Boa, I really did not pay attention to this detail of the question, it follows: http://sqlfiddle.com/#! 17/d61ef/15

  • Thank you very much for the aid!

1 answer

1


You need to Use Max and Min to get the dates of the maximum and minimum groups the Equipment and use the having to check which are with quantity greater 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
  • 3

    The having will not recognize the alias Quantidade. Will give error. It is necessary to replace having Quantidade for having count(*)

  • And I might not include in COUNT(*) if the date is repeated for the same equipment?

Browser other questions tagged sql select-sql

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