Using having in SQL

Question

I want to merge the following queries to get the answer I want. But I don't seem to get it right.

Query 1:

select a.idlisting, count(p.idpostulant) as Total_postulants
from postulations p
inner join listings a on p.idlisting = a.idlisting
where a.idate >= '2014-07-01'
and a.idate < '2014-08-01'
group by a.idlisting
having total_postulants > 500

With the answer I get from the first query I complete the idlisting clause

**Query 2:**

select count(distinct p.idpostulant)
from postulations p
inner join listings a on p.idlisting = a.idlisting
where a.idlisting in ('1001972582',
'1001964448',
'1001926936')

The answer should be the amount of unique postulants that subscribe to all the listings that have over 500 postulations.

Please let me know if it's understandable.

Thanks for the help.

bowlturner · Answer 1 · 2014-08-07T19:53:41.303

1

Just putting the first query in the 'in' clause should work. Or are you looking for a cleaner version?

select count(distinct p.idpostulant)
from postulations p
inner join listings a on p.idlisting = a.idlisting
where a.idlisting in (
      select a.idlisting
      from postulations p
      inner join listings a on p.idlisting = a.idlisting
      where a.idate >= '2014-07-01'
      and a.idate < '2014-08-01'
      group by a.idlisting
      having count(p.idpostulant) > 500)

edited Aug 07 '14 at 19:53

answered Aug 07 '14 at 19:42

bowlturner

1,968
4
23
35

Nop, that throws an error. Operand should contain 1 column – Pietro Aug 07 '14 at 19:47
oops, forgot to remove the second column I'll fix it. – bowlturner Aug 07 '14 at 19:48
No, error again. total_postulants is an alias from the count function. – Pietro Aug 07 '14 at 19:51
made one more change to the having clause – bowlturner Aug 07 '14 at 19:53
your answer should work, but the problem is that is taking too long to fetch me the result. I didn't clarify that both tables are very big. Aprox 20GB and 60GB – Pietro Aug 07 '14 at 20:02
I could see that being a problem – bowlturner Aug 07 '14 at 20:05

Gordon Linoff · Accepted Answer · 2014-08-07T20:11:58.627

Just use join and subqueries:

select count(distinct p.idpostulant)
from postulations p inner join
     listings a
     on p.idlisting = a.idlisting inner join
     (select a.idlisting, count(p.idpostulant) as Total_postulants
      from postulations p inner join
           listings a
           on p.idlisting = a.idlisting
      where a.idate >= '2014-07-01' and a.idate < '2014-08-01'
      group by a.idlisting
      having total_postulants > 500
     ) filter
     on a.idlisting = filter.idlisting;

EDIT:

You can slightly simplify the above query by removing one of the joins in the outer query:

select count(distinct p.idpostulant)
from postulations p inner join
     (select a.idlisting, count(p.idpostulant) as Total_postulants
      from postulations p inner join
           listings a
           on p.idlisting = a.idlisting
      where a.idate >= '2014-07-01' and a.idate < '2014-08-01'
      group by a.idlisting
      having total_postulants > 500
     ) filter
     on p.idlisting = filter.idlisting;

Your query worked, but it took two minutes to fetch the answer. I'll try the other answer I have left to see which one is more efficient. — Pietro, Aug 07 '14 at 20:04
@Pietro . . . It is hard to know if that is reasonable without knowing about the size of your current data and the indexing structure. — Gordon Linoff, Aug 07 '14 at 20:10
One of the tables is big, lisitng is 7GB and postulations 80GB — Pietro, Aug 07 '14 at 20:16

Using having in SQL

2 Answers2