Re: R: Help with Sql

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