afficher 2 count de de tables

[vacataire testeur de site web]

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

[Tonkuma]

Could you describe by English?
Or, show sample source data and expected results.

[vacataire testeur de site web]

"Tonkuma" <to*****@jp.ibm .com> wrote in message news:<11******* **************@ o13g2000cwo.goo glegroups.com>. ..

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.custom er)...from t1,t2

and having in result

num count(t1.car) count(t2.custom er)
1 5 6
2 4 3
3 0 1

thanks
Thierry

[Philip Sherman]

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(count 2,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

[vacataire testeur de site web]

Thanks I have not the time to test now but it seems really good.
Thierry

Philip Sherman <ps******@ameri tech.net> wrote in message news:<hL******* ********@newssv r19.news.prodig y.com>...

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(count 2,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