Re: R: Help with Sql

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Hello Gio

It helps to look at this as sets of things. So you want to include only 
rows where the number of rows with combination of codevet & nro_inv is 1 -

     select codevet, nro_inv from table group by codevet, nro_inv 
having count(*) = 1

This gives result like this -

nro_inv
000   63509
046   75569
046   75978
046   76078

Then you want the counts of codeVet in the list we just collected -

     select codeVet, count(*) from (select codevet, nro_inv from table 
group by codevet, nro_inv having count(*) = 1)

There are some assumptions here - do you need to include the year 
somewhere? Can the same nro_inv be used in different years?

HTH
Vern

On 5/24/2023 1:06 PM, gio.cot via MIDRANGE-L wrote:
> Sorry  .
>
> I hope the table now is more clear
>
>   
>
> codeVet       Year_Inv        nro_inv          type_inv
>
> 000                    23               63509             G
>
> 046                   23               71490            G
>
> 046                   23               71490             H
>
> 046                   23                75569             G
>
> 046                   23                75978            G
>
> 046                    23              76078            G
>
>   
>
> Hi all
>
>   
>
> Suppose i have a table with this data
>
>   
>
>
> codeVet
>
> Year_Inv
>
> nro_inv
>
> type_inv
>
>
> 000
>
> 23
>
> 63509
>
> G
>
>
> 046
>
> 23
>
> 71490
>
> G
>
>
> 046
>
> 23
>
> 71490
>
> H
>
>
> 046
>
> 23
>
> 75569
>
> G
>
>
> 046
>
> 23
>
> 75978
>
> G
>
>
> 046
>
> 23
>
> 76078
>
> G
>
>   
>
> With Sql i would get :
>
>   
>
> The nro Invoice for every CodeVet_vet excluding  the nro_inv the have two or
> more row (in this case the nro_inv 23-71490) ; so the result that i would
> need, is
>
>   
>
> CodeVet  Nro_inv
>
> 000              1
>
>      046              3
>
>   
>
> Thanks in advance
>
> Gio
>
>   
>
>