how can i find tripled values based on 2 columns-CodePudding

I have a database called "Delivered" with several fields such as: ID - Carrier - Date - Activated - Total Weight

I would like to obtain a result in which if the Total Weight was a tripled value of the same carrier and if it was activated = 1

ID      Carrier                                         Activated   Total Weight
324197  Filipa Carlos Alves - Pavimentos Unipessoal, AAA.   0           420
324204  Filipa Carlos Alves - Pavimentos Unipessoal, AAA.   0           820
324704  Francisca José Vieira Oliveira                      0            40
325152  FINIS - Respduos Industriais Unipessoal, AAA.       1           360
325464  FINIS - Respduos Industriais Unipessoal, AAA.       1           260
325531  Francisco José Vieira Oliveira                      0           200
326152  FINIS - Respduos Industriais Unipessoal, AAA.       1            20
326157  FINIS - Respduos Industriais Unipessoal, AAA.       1           120
326293  Francisca José Vieira Oliveira                      0           100
326325  F. P. & Pinto, Lda.                                 0           700
326563  FINIS - Respduos Industriais Unipessoal, AAA.       1           240
326564  FINIS - Respduos Industriais Unipessoal, AAA.       1           120
326914  FINIS - Respduos Industriais Unipessoal, AAA.       1            80
326921  FINIS - Respduos Industriais Unipessoal, AAA.       1           120

and the result should be

ID      Carrier                                         Activated   Total Weight
326157  FINIS - Respduos Industriais Unipessoal, AAA.       1           120
326564  FINIS - Respduos Industriais Unipessoal, AAA.       1           120
326921  FINIS - Respduos Industriais Unipessoal, AAA.       1           120

What is the best way to do it

CodePudding user response：

You can use COUNT() as a window function to find the repeated rows. For example:

select *
from (
  select t.*, count() over(partition by carrier, total_weight) as cnt
  from delivered t
  where activated = 1
) x
where cnt = 3

CodePudding user response：

Mentioning what RDBMS you use is important, but hopefully you can work off of this example.

WITH calculated_duplicates AS (
 SELECT 
  [ID],
  [Carrier], 
  [Activated],
  [Total Weight],
  COUNT(DISTINCT [Total Weight]) OVER (PARTITION BY [Carrier]) as number_of_duplicate_weights_per_carrier
 FROM [your_table]
 WHERE [Activated] = 1)

SELECT 
  [ID],
  [Carrier], 
  [Activated],
  [Total Weight] 
FROM calculated_duplicates 
WHERE number_of_duplicate_weights_per_carrier = 3

CodePudding user response：

I believe you just need a correlated exists check:

select * 
from Delivered d
where d.Activated = 1 and exists (
  select * from Delivered d2 
  where d2.carrier = d.carrier 
    and d2.Activated = 1 
    and d2."Total Weight" = d."Total Weight" * 3
);