Could you describe by English?
Or, show sample source data and expected results.
vacataire testeur de site web - 11 Apr 2005 07:57 GMT
> Could you describe by English?
> Or, show sample source data and expected results.
Sory, I try to
I want to know if in sql it is possible to use count function with 2 tables.
Something like select t1.num, count(t1.car), count(t2.customer)...from t1,t2
and having in result
num count(t1.car) count(t2.customer)
1 5 6
2 4 3
3 0 1
thanks
Thierry
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
> Bonjour je cherche à afficher de count de tables identiques dans la forme
>
[quoted text clipped - 10 lines]
> merci pour votre aide
> thierry
vacataire testeur de site web - 19 Apr 2005 13:01 GMT
Thanks I have not the time to test now but it seems really good.
Thierry
> 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:
[quoted text clipped - 57 lines]
> > merci pour votre aide
> > thierry