CODEWITHSUNDEEP
sqlsql-server
khgove
khgove

Reputation: 69

Query from multiple tables with multiple where conditions in the tables

I'm trying to get a count of all speakers who are active regarding that item as well as the total of speakers who correlate to a certain item. The first LEFT JOIN for the total speakers works, but the other for ONLY the active speakers regarding that item doesn't, any help is appreciated. The SQLFiddle is here

http://sqlfiddle.com/#!3/b579d/1

But when I try to add in the portion where you would get the number of active speakers

(LEFT JOIN (SELECT COUNT (tbl_SpeakerCard_Log.SpeakerName) 
WHERE tbl_Speaker_Log.Spoken = 0) 
ON tbl_AgendaList.AID = tbl_SpeakerCard_Log.AID)

under the previous LEFT JOIN I get an error. I'm 100% sure the query is wrong in some form, but I'm not sure how to approach it.

*NOTE: Spoken/Active are interchangeable, I just use different wording to clarify what I'm looking for.

EDIT: This is the desired output

https://i.sstatic.net/bejC8.jpg

Upvotes: 0

Views: 34

Answers (2)

jpw
jpw

Reputation: 44871

You can use conditional aggregation to do this:

SELECT    
    AgendaList.AID, 
    AgendaList.Item, 
    COUNT(SpeakerList.SPID) as SpeakerTotal, 
    SUM(CASE WHEN SpeakerList.Spoken = 0 THEN 1 ELSE 0 END) as ActiveSpeakers
FROM      AgendaList 
LEFT JOIN SpeakerLIST ON AgendaList.AID = SpeakerList.AID
GROUP BY  AgendaList.AID, AgendaList.Item;

Sample SQL Fiddle

Or you could use count instead of sum (which might be clearer):

COUNT(CASE WHEN Spoken = 0 THEN Spoken END) as ActiveSpeakers

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

SQL FIDDLE

WITH sTotal AS (
SELECT    AgendaList.AID, AgendaList.Item, COUNT( SpeakerList.SPID) as SpeakerTotal
FROM      AgendaList 
LEFT JOIN SpeakerLIST ON AgendaList.AID = SpeakerList.AID
GROUP BY  AgendaList.AID, AgendaList.Item
),
sActive AS (
SELECT    AgendaList.AID, AgendaList.Item, COUNT( SpeakerList.SPID) as SpeakerActive
FROM      AgendaList 
LEFT JOIN SpeakerLIST ON AgendaList.AID = SpeakerList.AID
WHERE SpeakerLIST.Spoken = 0
GROUP BY  AgendaList.AID, AgendaList.Item
)
SELECT sTotal.*, sActive.SpeakerActive
FROM sTotal left join
     sActive on sTotal.AID = sActive.AID

Upvotes: 1

Related Questions