I am running a query that returns the results seen below:

SELECT t1.col_a, t2.doc_id, t1.col_c
FROM dbo.table1 t1
LEFT JOIN dbo.table2 t2 ON t1.id = t2.id
WHERE t1.col_c = 'test'


########### RESULTS #########
| col_a |  doc_id |  col_c  |
-----------------------------
|content|    1    |    test |
|content|    2    |    test |
|content|    3    |    test |

Sample Data

This is an example of data I am working with:

############### TABLE 1 #######################
|   col_a  |  col_b  |   col_c  | id
---------------------------------------
| content  | content | test     |  22
| content  | content | test     |  23
| content  | content | asdf     |  24
| content  | content | test     |  25
| content  | content | asdf     |  26
| content  | content | qwer     |  27
| content  | content | asdf     |  28
| content  | content | test     |  29


############### TABLE 2 #######################
| t2_col_a |   doc_id  | t2_col_b| t2_col_c | id
-----------------------------------------------
| content  |     1     | content | content  | 22
| content  |     2     | content | content  | 23
| content  |     3     | content | content  | 24
| content  |     3     | content | content  | 25
| content  |     3     | content | content  | 26
| content  |     1     | content | content  | 27
| content  |     2     | content | content  | 28
| content  |     2     | content | content  | 29

What I would like to do is include a COUNT of all doc_id's located in table2. For example:

######## DESIRED RESULTS ##########
| col_a |  doc_id | col_c | count |
------------------------------------
|content|    1    |  test |   2   |
|content|    2    |  test |   3   |
|content|    3    |  test |   3   |

I want to count all the records that are associated with each doc_id found in initial results (for example, there may be 500 records in table2 that have a doc_id of 1). The records I want to get a count of are found in table2.

I tried simply adding COUNT (id) after my SELECT statement, but that just returns 1 for each row, because it's counting the results from the query. How do I get the total count of records for each doc_id from table2 within my query?

CodePudding user response：

Is col_b stored as an integer? IF so, SUM() will do the trick. It might work with a varchar too but will be less efficient. Something like this:

SELECT t1.col_a, SUM(t2.col_b)
FROM dbo.table1 t1
LEFT JOIN dbo.table2 t2 ON t1.id = t2.id
WHERE t1.col_c = 'value'
GROUP BY t1.col_a

If you are not aware you will need to add the GROUP BY to use SUM for columns not in the SUM.

CodePudding user response：

select   max(t.col_a) as col_a
        ,max(t.col_c) as col_c
        ,t2.doc_id
        ,count(*)     as count
from     t join t2 on t2.id = t.id
group by t2.doc_id

Fiddle