How to Use HAVING in a Subquery as Part of a Select Statement

Question

I'm working on a query to return how many activists have volunteered this year by team more than once; I got it to work as a standalone query:

   SELECT Activists.team, COUNT(id) AS Activists
    FROM
    (
        SELECT e.id, v.team, 
        COUNT(CASE WHEN (e.status="Completed" AND right(e.date,2)="15") THEN e.id END) AS count
        FROM actiontable e
        LEFT JOIN persontable v ON v.id = e.id
        GROUP BY e.id, v.team
        HAVING (count > 1)
    ) AS Activists
    GROUP BY Activists.team;

But I can't quite figure out how to get it to work in a longer SELECT statement. The problem I'm running into is that I have many other (more simple) parts of the query to return things by team also in the SELECT statement like:

COUNT(DISTINCT CASE WHEN v.WillCanvass = "X" THEN v.id END)

So obviously I can't have the HAVING (count > 1) part of the query for the activists because then it would affect all the other parts of my SELECT statement -- so I need the subquery above to only affect the sole part where I'm working on.

I made a SQL Fiddle with sample schema to help with the above query that works -- but the ideal would be to get an output that looks similar to this, where the Activists subquery doesn't affect the WillCanvass column (even though I made up the numbers below):

Team    Activists   WillCanvass
Team A  2           2
Team B  8           5
Team C  7           3

Hope that makes sense -- thanks!

---

EDIT

My best shot at what I want -- though the query gives me errors -- looks like this:

SELECT a.team as team,
    COUNT(v.*) as activists,
    SUM(CASE WHEN v.WillCanvass = "X" THEN 1 ELSE 0 END) as WillCanvass
FROM
persontable v
left join
(
    SELECT e.id, 
        v.team,
        v.WillCanvass,
        COUNT(*) as count
    FROM actiontable e
    LEFT JOIN persontable v ON v.id = e.id
    WHERE e.status="Completed" AND right(e.date,2)="15"
    GROUP BY e.id
    HAVING (count > 1)) as a 
        GROUP BY team;

There's an updated SQL Fiddle of it here.

Answer 1

I got it -- thanks for helping me get there, but it's pretty different than anything above. I had to take the HAVING clause out completely in order have the first part of the SELECT statement (WillCanvass) be completely independent from the actiontable part of it:

SELECT a.team as team,
    COUNT(case when a.X > 1 then a.id else null end) as activists,
    SUM(CASE WHEN a.WillCanvass = "X" THEN 1 ELSE 0 END) as WillCanvass
FROM (
    SELECT v.id, 
        v.team,
        v.WillCanvass,
        COUNT(case when e.status="Completed" AND right(e.date,2)="15" then e.id else null end) as X
    FROM actiontable e
    RIGHT JOIN persontable v ON v.id = e.id
    GROUP BY v.id, v.team, v.WillCanvass
 ) as a 
GROUP BY team;

And here's a new SQL Fiddle to see how it works.

Answer 2

try this way:

http://sqlfiddle.com/#!2/e186da/5

SELECT  v.team as team, 
        SUM(CASE WHEN (e.status="Completed" AND YEAR(e.date)="2015") THEN 1 ELSE 0 END) AS count
        FROM actiontable e
        LEFT JOIN persontable v ON v.id = e.id
        GROUP BY v.team
        HAVING (count > 1);


SELECT  v.team as team, 
        SUM(CASE WHEN (e.status="Completed" AND YEAR(e.date)="2015") THEN 1 ELSE 0 END) AS count,
        SUM(CASE WHEN (v.WillCanvass='X') THEN 1 ELSE 0 END) AS WillCanvass
        FROM actiontable e
        LEFT JOIN persontable v ON v.id = e.id
        GROUP BY v.team

Answer 3

Not sure exactly what you try to achieve. I first get the list of activists with the right criteria and then GROUP BY team.

SELECT a.team as team,
    COUNT(*) as activists,
    SUM(CASE WHEN a.WillCanvass = "X" THEN 1 ELSE 0 END) as WillCanvass
FROM (
    SELECT e.id, 
        v.team,
        v.WillCanvass,
        COUNT(*) as count
    FROM actiontable e
    LEFT JOIN persontable v ON v.id = e.id
    WHERE e.status="Completed" AND right(e.date,2)="15"
    GROUP BY e.id
    HAVING (count > 1)) as a 
GROUP BY team