Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / April 2005

Tip: Looking for answers? Try searching our database.

afficher 2 count de de tables

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
vacataire testeur de site web - 09 Apr 2005 11:43 GMT
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 - 10 Apr 2005 11:08 GMT
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
Philip Sherman - 12 Apr 2005 18:41 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.