Check the frequency of records in the table

Asked

Viewed 711 times

0

I have a table where are saved all services performed in several cars. The key is the car plate. A car can have more than one service performed per 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’t because it’s inaccurate to save an id for each service.

Ex: car aaa1234 in the days 01,02,03 made 3 types of services per day. car bbb4321 on the days 01,02,03 made 1 type of service per day.

If I’m right, in my query, you’re accusing that the car aaa1234 is the most frequent. Since he did not want to count by quantity of service, but by frequency.

I’m not as skilled with querys and I can’t outrun that scope:

SELECT count(placa), placa
  FROM [producao].[dbo].[servicos_teste]
  group by placa
  order by COUNT(placa) DESC
sql sql-server query filter sql-server-express

  • that’s right, but you already count the board once, you don’t need to repeat the command just give an alias to the COUNT and sort by it: SELECT Count(board) AS Qtd, board FROM [production]. [dbo]. [servicos_test] GROUP BY board ORDER BY Qtd DESC

1 answer

-1


Considering the existence of a column indicating the date of entry of the vehicle into the workshop, one option is to count the number of different entry dates for each 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 service order be issued for each vehicle entry into the workshop. Each OS may contain one or more services to be performed in the vehicle. Then change the table structure servicos_test to contain column with service order numbering (OS). This way it is more reliable, as just count the amount of OS open for each board.

Browser other questions tagged sql sql-server query filter sql-server-express

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