Query from multiple tables with multiple where conditions in the tables

Question

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

Sorry about that, just edited in a table with the output desired @JuanCarlosOropeza — khgove
– khgove, Commented Aug 14, 2015 at 21:35
How you get active 3 and 2, when only one speaker have Spoken bit =1 in your sample data? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Aug 14, 2015 at 21:37
If spoken is 0, I'd like it to be on the active list, I sorta phrased that wrong — khgove
– khgove, Commented Aug 14, 2015 at 21:41

jpw · Accepted Answer · 2015-08-14 21:47:47Z

1

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

edited Aug 14, 2015 at 21:47

answered Aug 14, 2015 at 21:42

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

khgove Over a year ago

A lot of things I did not know you could in MSSQL... Or DBs in general. Thanks a bunch, I can finally progress a bit more in my project now :D

Juan Carlos Oropeza · Accepted Answer · 2015-08-14 21:44:37Z

1

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

answered Aug 14, 2015 at 21:44

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

Query from multiple tables with multiple where conditions in the tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related