T.Els T.Els - 7 months ago 10
SQL Question

sql sum count(*) of two tables

I have two tables

w300
and
v600
and I would like to count the number of the machines. The problem is to count only the machines which are combined with the table field
v600.vb
.

Table v600 fields: vb, konto, satzart, fi_nr

Table w300 fields: maschnr, konto, satzart, fi_nr,

fi_nr[Key]
konto[Key]
satzart[Key]


For example:
I wanna

select 'Anz. Machine: ', count(*) \
from w300 \
where length(w300.maschnr) in (6,7) \
and w300.maschnr LIKE ('%M%') \
and w300.maschnr not LIKE ('%FLP%') \
and w300.maschnr not LIKE ('%DFS%') \
and w300.maschnr not LIKE ('%SB%') \
and w300.maschnr not LIKE ('%C%') \
and w300.maschnr not LIKE ('%DL%') \
and w300.konto = 8000031 \
and w300.satzart = 1 \
and w300.fi_nr = 1 \


but only these where
v600.vb = 8000001.


I don't get it.
Thank you.

I added you some pictures.

Example

Example

Tables

Result

The result should be if
w300.konto = 8000001
and
v600.vb = 8000001
the amount of Anz. Machines = 60

select * from v600 where vb = 8000001;

v600 CUSTOMERS
fi_nr satzart konto vb

1 1 8000034 8000001

1 1 8000039 8000001

1 1 8000219 8000001

1 1 8000031 8000001

1 1 8000001 8000001

1 1 8000037 8000001

select * from w300 where konto = 8000039;

w300 MACHINES
fi_nr maschnr identnr satzart konto

1 #M2684D #M2684D 1 8000039

1 #M2719D #M2719D 1 8000039

select * from w300 where konto = 8000219;

w300 MACHINES
fi_nr maschnr identnr satzart konto

1 #M2581 #M2581 1 8000219

1 #M2591 #M2591 1 8000219

1 #M2599 #M2599 1 8000219

1 #M2600 #M2600 1 8000219

1 #M2601 #M2601 1 8000219

1 #M2604 #M2604 1 8000219

1 #M2605 #M2605 1 8000219

1 #M2606 #M2606 1 8000219


select 'Anz. Machine: ', count(*)
from w300
where length(w300.maschnr) in (6,7)
and w300.maschnr LIKE ('%M%')
and w300.maschnr not LIKE ('%FLP%')
and w300.maschnr not LIKE ('%DFS%')
and w300.maschnr not LIKE ('%SB%')
and w300.maschnr not LIKE ('%C%')
and w300.maschnr not LIKE ('%DL%')
and w300.konto = 8000039
and w300.satzart = 1
and w300.fi_nr = 1 ;

plus:

select 'Anz. Machine: ', count(*)
from w300
where length(w300.maschnr) in (6,7)
and w300.maschnr LIKE ('%M%')
and w300.maschnr not LIKE ('%FLP%')
and w300.maschnr not LIKE ('%DFS%')
and w300.maschnr not LIKE ('%SB%')
and w300.maschnr not LIKE ('%C%')
and w300.maschnr not LIKE ('%DL%')
and w300.konto = 8000219
and w300.satzart = 1
and w300.fi_nr = 1 ;

plus:

select 'Anz. Machine: ', count(*)
from w300
where length(w300.maschnr) in (6,7)
and w300.maschnr LIKE ('%M%')
and w300.maschnr not LIKE ('%FLP%')
and w300.maschnr not LIKE ('%DFS%')
and w300.maschnr not LIKE ('%SB%')
and w300.maschnr not LIKE ('%C%')
and w300.maschnr not LIKE ('%DL%')
and w300.konto = 8000034
and w300.satzart = 1
and w300.fi_nr = 1 ;

= Anz. Machine where v600.vb = 8000001

Answer

Based on your edit, I think you might be looking for something like

select 'Anz. Machine: ', count(*)           
from   w300                                 
where  length(w300.maschnr) in (6,7)        
...
and     w300.konto          IN (SELECT konto FROM v600 
                                 WHERE vb = 8000001)
... ;