Assuming you want to count the number of rows in each table that have
matching values for a single column (num_cli), you have the following to do:
1. get counts for each table
2. merge the results
This is complicated by the possibility of having non-matching values in
num_cli for the tables. Application data rules may simplify this - ie.
the voiture table cannot have a num_cli that does NOT exist in poste.
The following query (UDB 8.2) will generate your results, assuming
non-matching values in both tables. T1 and t2 are your two tables and
"num" is the matching column:
with temp1 as (select num as num1,count(*) as count1
from t1 group by num),
temp2 as (select num as num2, count(*) as count2
from t2 group by num),
temp3 as ( select num1 as num from temp1
union select num2 as num from temp2)
select num
,value(count1,0) as "nombre de poste"
,coalesce(count2,0) as "nombre de voiture"
from temp3
left outer join temp1 on num = num1
left outer join temp2 on num = num2
order by num
;
source data:
insert into t1 values(1),(1),(2),(3),(3),(3),(4),(4),(5)
insert into t2 values(1),(1),(1),(3),(3),(3),(4),(5),(5),(6),(6)
results:
NUM nombre de poste nombre de voiture
----------- --------------- -----------------
1 2 3
2 1 0
3 3 3
4 2 1
5 1 2
6 0 2
6 record(s) selected.
Phil Sherman
vacataire testeur de site web wrote:
Bonjour je cherche à afficher de count de tables identiques dans la forme
exemple
num_cli | nombre de poste | nombre de voiture
1 1 3
2 2 5
3 1 0
45 4 6
deux tables poste et voitures par exemple
merci pour votre aide
thierry
