Select script to include count of another table?

Question

I'm trying to figure out how to write a script to select certain records/fields from one table, then join it to another table and include a column for a corresponding count. For example, this is what I have now:

select ID, Val from OriginalTable where Something = 1

Now I have another table which has records linking to this original table, where the a column OriginalID matches the ID of the OriginalTable. One example would be:

select * from OtherTable where OriginalID = 3

...where 3 = the ID of the OriginalTable.

What I'd like to do is add a column to the first statement which shows a count of the other table as shown in the second query. For example, this might be a result set:

ID    Val    Count
1     abc    15
2     def    23
3     ghi    42
4     jkl    19

How do I write this query to include this count?

Jon Egerton · Accepted Answer · 2012-08-14T15:34:32.617

2

There are a lot of ways to do this, but you could either use a Group By

select ID, Val, [Count] = count(1)
from OriginalTable be
left join OtherTable ot on be.OriginalID = ot.OriginalID
where Something = 1
group by ID,Val

or you could use OVER and Partition By:

select ID, Val, [Count] = count(1) OVER(PARTITION BY ID,Val)
from OriginalTable be
left join OtherTable ot on be.OriginalID = ot.OriginalID
where Something = 1

Personally I like the OVER method. There's information on MSDN here.

edited Aug 14 '12 at 15:34

answered Aug 14 '12 at 15:28

Jon Egerton

40,401
11
97
129

In your answer what does the 1 in `count(1)` represent? I'm trying to use your example as I think its what I need, but I am getting many results(duplicate rows) returned for each row now with the `OVER` method but in my query instead of 1 I have the column I want it to count – ScottC Oct 20 '17 at 12:48
1

Count(1) or Count(*) are interchangeable, but different from Count(col) which will not include nulls in the column. See https://stackoverflow.com/questions/1221559/count-vs-count1. There may once have been a time when they were different (early oracle or sql server) – Jon Egerton Oct 24 '17 at 09:25

score 2 · Answer 2 · answered Aug 14 '12 at 15:29

2

A subquery should handle that for you:

SELECT 
  ID, Val, Cnt
FROM OriginalTable
JOIN (
  SELECT 
    OriginalID, COUNT(*) AS Cnt
  FROM OtherTable
  GROUP BY OriginalID
  ) AS Sub ON ID=OriginalID

answered Aug 14 '12 at 15:29

Jim

3,482
22
18

This ended up being the example that worked best for me, thanks. Using SQL Report Builder seemed to not like the `Over Partitioned By` method above – ScottC Oct 24 '17 at 13:10

score 1 · Answer 3 · answered Aug 14 '12 at 15:28

1

;WITH ot(ID, c) AS 
(
  SELECT OriginalID, COUNT(*)
   FROM dbo.OtherTable
   GROUP BY OriginalID
)
SELECT t.ID, t.Val, [Count] = COALESCE(ot.c, 0)
  FROM dbo.OriginalTable AS t
  LEFT OUTER JOIN ot
  ON t.ID = ot.ID
  WHERE t.Something = 1;

answered Aug 14 '12 at 15:28

Aaron Bertrand

272,866
37
466
490

score 1 · Answer 4 · answered Aug 14 '12 at 15:29

1

SELECT be.ID, be.Val, Count(*) AS [Count]
FROM OriginalTable be
LEFT OUTER JOIN OtherTable ot ON be.ID = ot.OriginalID
WHERE be.Something = 1
GROUP BY be.ID, be.Val

answered Aug 14 '12 at 15:29

lc.

113,939
20
158
187

Awkward how `be` actually perfectly matches what I intended to convert it to in the first place :D – Jerry Dodge Aug 14 '12 at 17:49
1

Ah, oops, I forgot to remove that actually, I tried to re-write it excluding business specific stuff, anyway, that works for me, now which answer to accept? – Jerry Dodge Aug 14 '12 at 19:23

Select script to include count of another table?

4 Answers4