How do I get the count of rows from two different tables in one result?

Asked Mar 30 '14 at 06:55

Active Mar 31 '14 at 00:13

Viewed 114 times

I have two tables from which I need to extract the NUMBER of rows belonging to the same ID.

Orders Table
Positions Table

I'm trying to build a query that will get me the amount of matching rows from the two tables, based on a common ID. I'm not looking for a result set that will return all matching rows, I only need back one value with the amount of matching rows found.

I've tried this next query, but I know there are in fact 48 rows when it returns 50.

 select Isnull(CntPos,0)+IsNull(CntOrd,0)
 from  (select CID
             ,Count(*) CntPos  
        from  [Serv].[Trade].[Position] 
        Group By CID
       ) Pos
 Full join (select CID
                ,Count(*) CntOrd  
            from  [Serv].[Stocks].[Orders] 
            Group By CID
           ) Ord 
     on Ord.[CID] = Pos.CID
 WHERE Ord.CID=19556

edited Mar 31 '14 at 00:13

Daniel E.

1,999
3
20
28

asked Mar 30 '14 at 06:55

Yosi199

1,685
4
21
45

2

Possible similar [question](http://stackoverflow.com/questions/1279569/sql-combine-select-count-from-multiple-tables). – Nathan Mar 30 '14 at 07:01
Thank you. That was all I needed. – Yosi199 Mar 30 '14 at 07:18
Duplicate of this > http://stackoverflow.com/questions/1279569/sql-combine-select-count-from-multiple-tables – Yosi199 May 08 '14 at 05:10

How do I get the count of rows from two different tables in one result?

0 Answers0