Check frequency of records in the table

Question

Check frequency of records in the table

Navigation

#1 by (-1 votes)

0

I have a table where all services performed on several cars are saved. The key is the car license plate. A car can have more than one service a day. And for each service performed a new service id is created, even if it is for the same car.

I need to filter the 100 most frequent cars and I can not, therefore, it is inaccurate for saving an id for each service.

Ex: car aaa1234 on the days 01.02.02 made 3 kinds of services per day. car bbb4321 on days 01.02.03 did 1 type of service per day.

If I am right, in my query, it is accusing that the aaa1234 car is the most frequent one. Since I did not want to count by quantity of service, but by frequency.

I'm not very skilled with querys and I can not escape this scope:

SELECT count(placa), placa
  FROM [producao].[dbo].[servicos_teste]
  group by placa
  order by COUNT(placa) DESC

sql sql-server query filtro sql-server-express

asked by anonymous 23.11.2017 / 20:06

1 answer

error Recoverable fatal error: Object of class mysqli_result could not be converted to string Can you do self-executable script in Python? (Autorun)

score -1 · Accepted Answer

Considering the existence of a column that indicates the date of entry of the vehicle into the workshop, one option is to count the number of different entry dates for each license plate.

-- código #1 v2
SELECT top (100) placa, count(distinct data_entrada) as Frequência
  FROM [producao].[dbo].[servicos_teste]
  group by placa
  order by Frequência desc;

I suggest that a work order be opened at each workshop entrance. Each OS may contain one or more services to be performed on the vehicle. Then change the structure of the servicos_teste table to contain column with work order numbering (OS). This way it is more reliable, as it is enough to count the amount of OS opened for each board.