SQL Server : query with subquery involving select from previous selection

Question

I'm trying to create a query in SQL Server to determine how many times a person's name shows up in a list, but also that list will be unknown, so I would have to get the actual name from the previous select index... It's hard to explain so I'll show the query first and hopefully someone can help.

SELECT 
    SpeakerName, Spoken,
    (SELECT COUNT(SpeakerName)
     FROM tbl_SpeakerCard_Log
     WHERE SpeakerName = 'SpeakerName[i]' AND SpeakDate = '3-9-16') as TimesSpoken
FROM 
    tbl_SpeakerCard_Log
WHERE 
    AID = ####
ORDER BY 
    GeneralComment ASC

So basically, in SpeakerName[i], I'd like to somehow get the SpeakerName from the outer Select. The output should come out something like this

+-------------+--------+-------------+
| SpeakerName | Spoken | TimesSpoken |
+-------------+--------+-------------+
| Holly       |      0 |           4 |
| Robert      |      1 |           5 |
| Mike        |      1 |           2 |
+-------------+--------+-------------+

Possible duplicate of [Count number of occurrences for each unique value](http://stackoverflow.com/questions/13235981/count-number-of-occurrences-for-each-unique-value) — Tab Alleman, Mar 11 '16 at 19:14

score 0 · Answer 1 · answered Mar 11 '16 at 19:16

0

Try this:

select x.SpeakerName, x.Spoken, COUNT(*) as TimesSpoken
from tbl_SpeakerCard_Log x
WHERE AID = ####
and x.SpeakDate = '3-9-16'
group by x.SpeakerName, x.Spoken

Don't have SSMS installed on this computer so can't test it.

answered Mar 11 '16 at 19:16

Mark Pim

9,898
7
40
59

SQL Server : query with subquery involving select from previous selection

1 Answers1