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> On Behalf Of
jeremy.ruth@xxxxxxxxxxx
Sent: Friday, May 26, 2023 7:54 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: 'Vern Hamberg' <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> On Behalf Of Vern
Hamberg via MIDRANGE-L
Sent: Thursday, May 25, 2023 5:13 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Vern Hamberg <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 To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: 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 for any subscription related
questions.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: 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
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
