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.

@Alex Sorry, it's embedded: [http://sqlfiddle.com/#!2/8f4e1d/2](http://sqlfiddle.com/#!2/8f4e1d/2) — Ryan, Feb 25 '15 at 17:18
not related to your problem, but why did you repeat fields `LastName` and `FirstName` in both tables ? — MamaWalter, Feb 25 '15 at 17:35
@Alex I know date is in `VARCHAR` and it should be as a date -- but from where I import it from, it gets all weird when it's not `VARCHAR`, so I just left it as that. But the `RIGHT(date,2)="15"` formula should still work regardless. And if you look below, I commented on your query in the answer section. — Ryan, Feb 25 '15 at 17:39
@MamaWalter No real reason -- the `ID`s are all you need to match them up; I just extract and import them from a place that already gives you last/first so I just leave it there instead of deleting them every time -- and it makes it easier to reference who did what when I look at just that table. — Ryan, Feb 25 '15 at 17:40
@Ryan there is no varchar in your schema on sqlfiddle. so check my updated answer. check my sqlfiddle. tell me what is wrong with results I have? According to the data you provided you can't get result values you posted: `Team A 2 2`, `Team B 8 5`, `Team C 7 3` so check my fiddle and tell me what do you want to achieve? — Alex, Feb 25 '15 at 17:47
@Alex I added my best attempt at what I want from the query (even though I get errors) above if it helps. — Ryan, Feb 26 '15 at 01:27

score 3 · Answer 1 · answered Feb 25 '15 at 18:38

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

answered Feb 25 '15 at 18:38

MamaWalter

2,073
1
18
27

I think this is exactly what I need -- thanks! But I'm going to check it in a bit against my non-sample code. I was trying to achieve the `HAVING (count >1)` within the subquery instead of the entire query, but I kept running into errors. – Ryan Feb 25 '15 at 21:51
This is really close I think -- but I realized that the `WillCanvass` is coming from `a`, which means it's narrowing the people who are tagged with the `WillCavass` _"X"_ to the same criteria as the `a` table -- which means that the `WillCanvass` people also need to have more than one occurrence in the `actiontable` to get counted -- but I want them to be completely separate, so that everybody tagged with `WillCanvass` is counted regardless of anything to do with `actiontable`. Is that possible? – Ryan Feb 25 '15 at 22:10
Another way of saying all of that is that the obvious fix (if it worked) would be simply changing `a.WillCanvass` to `v.WillCanvass` -- but obviously, the way this query is built, SQL wouldn't understand `v.WillCanvass` in the field list. – Ryan Feb 25 '15 at 22:51

Alex · Answer 2 · 2015-02-25T18:41:29.960

0

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

edited Feb 25 '15 at 18:41

answered Feb 25 '15 at 17:09

Alex

16,739
1
28
51

Thanks! As I said above, the output numbers are made up -- so they don't have to match perfectly; it should just look similar to that. And your second `SELECT` statement above is pretty much exactly what I want the output to look like, except it's missing the `HAVING (count > 1)` from the first `SELECT` statement -- that's the part I was having trouble with. Can those two be combined? – Ryan Feb 25 '15 at 18:32
just use the last one if you need – Alex Feb 25 '15 at 18:40
The second one would be great -- but it counts the completed 2015 actions just as one, when the whole point of what I can't figure out is that I need to count only count completed 2015 actions that only happen more than once -- hence the `HAVING (count > 1)` part of the query. Though I can't do it for the entire query because then it would narrow the `WillCanvass` people as well. – Ryan Feb 25 '15 at 21:48

score 0 · Accepted Answer · answered Feb 27 '15 at 16:24

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.

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

3 Answers3