RE: R: Help with Sql

All good, Jeremy, you did uncover the problem that I had forgot to include "group by codevet"


Cheers
Vern


On Fri, 26 May, 2023 at 8:08 AM, jeremy.ruth@xxxxxxxxxxx <jeremy.ruth@xxxxxxxxxxx> wrote:


To: 'midrange systems technical discussion'

My apologies... Vern already had that. I didn't read all the way to the
end of Vern's reply.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of
jeremy.ruth@xxxxxxxxxxx<mailto:jeremy.ruth@xxxxxxxxxxx>
Sent: Friday, May 26, 2023 7:54 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Cc: 'Vern Hamberg' <vhamberg@xxxxxxxxxxxxxxx<mailto:vhamberg@xxxxxxxxxxxxxxx>>
Subject: RE: R: Help with Sql

Gio,

You can take Vern's solution 1 step further to get what you originally asked
for:

Select codevet, count(*)

From (

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

Jeremy

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Vern
Hamberg via MIDRANGE-L
Sent: Thursday, May 25, 2023 5:13 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx<mailto:vhamberg@xxxxxxxxxxxxxxx>>
Subject: 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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.