Write SQL count with HAVING sum restrictions in a JOIN without a sub-query in FROM

Question

Suppose there are two tables

    *   -----------------   1
site_visits                sites

site_id | visits          site_id      
1       | 15               1
1       | 10               2
2       | 20               3
2       | 45
3       | 55

The purpose is to count how many sites, have more than 50 visits. The DBMS is MySQL with InnoDB. To be noted, that there must be a join, because there are where clauses on columns from both tables (in the bigger picture).

I've managed to write this with a sub-query in FROM; from the above data, it should yield the value 2, since there are 2 such sites, with id 2 and 3 having sums 65 and 55, while for id 1 the sum is only 25.

select count(*) 
  from (
    select sum(visit.visits) as visits_sum
      from sites site 
      join site_visits visit
      on site.site_id = visit.site_id
      group by site.site_id) as sub_sum
  where visits_sum < 50

However I'd like this written without a sub-query in FROM in order to be able to use it with ORM; what I've managed thus far is:

select count(site.site_id)
  from sites site 
  join site_visits visit
  on site.site_id = visit.site_id
  group by site.site_id 
  having sum(visit.visits) < 50

Without the group by, it sums the whole joined table and yields 3. With it, it returns as many entries as the total count, in this case 2. And the values for these entries are 2 and 1 (since there are 2 entries with id 2 and 1 with id 3 in the joined table). Something like count(count(...)) would probably do it, but that's not allowed.

which DBMS are you using? – Radim Bača Nov 08 '17 at 08:01 — Radim Bača, Nov 08 '17 at 08:01
@RadimBača The DBMS is MySQL; I'll edit also the question. – Random42 Nov 08 '17 at 08:01 — Random42, Nov 08 '17 at 08:01
and which version of MySQL? Are you above 8.0.2? – Radim Bača Nov 08 '17 at 08:03 — Radim Bača, Nov 08 '17 at 08:03
Which DB? Mysql or SQL? – Vijunav Vastivch Nov 08 '17 at 08:08 — Vijunav Vastivch, Nov 08 '17 at 08:08

score 1 · Accepted Answer · answered Nov 08 '17 at 08:07

1

Ok, if you are using MySQL 8.0.2 and above then you can use window functions. i omit the site table since it is not really necessary for the example.

select distinct count(count(site_visits.site_id)) over ()
from site_visits
group by site_visits.site_id 
having sum(site_visits.visits) > 50

demo

answered Nov 08 '17 at 08:07

Radim Bača

10,646
1
19
33

@reds really why is that? – Radim Bača Nov 08 '17 at 08:10
1

Hibernate/JPA won't support analytic functions...so the OP won't be able to integrate this into his code base without using a raw query. – Tim Biegeleisen Nov 08 '17 at 08:10
Are you sure Mysql has this `over ()`? – Vijunav Vastivch Nov 08 '17 at 08:12
@reds http://mysqlserverteam.com/mysql-8-0-2-introducing-window-functions/ – Radim Bača Nov 08 '17 at 08:12
1

@reds Later versions of MySQL may support this, but the OP's aversion to a subquery is probably due to JPA, and using analytic functions makes this problem even harder. – Tim Biegeleisen Nov 08 '17 at 08:13
I think my bad cause i'm using lower version – Vijunav Vastivch Nov 08 '17 at 08:13
@TimBiegeleisen This may be true. However, it is a SQL solution without a subquery for an MySQL 8.0.2 and above. – Radim Bača Nov 08 '17 at 08:15
1

@RadimBača But I think it misses the point, because your query cannot be mapped directly to JPA...it would have to be a raw query, and therefore the entire code base would have a dependency on MySQL 8. – Tim Biegeleisen Nov 08 '17 at 08:17
@TimBiegeleisen made the correct assumptions. Unfortunately I cannot use this in JPA. And I also need the join, because of where with column from both tables (in the bigger picture) - was mentioned in the question. – Random42 Nov 08 '17 at 08:36
1

@m3th0dman fine, then I guess there is no solution in MySQL satisfying your requirements. – Radim Bača Nov 08 '17 at 08:39

score 0 · Answer 2 · answered Nov 08 '17 at 08:03

0

select count(site_id) from (
select site_id from site_visit group by site_id having sum(visits)>50)t

answered Nov 08 '17 at 08:03

Kapil

987
5
11

The join can be eliminated because sites not appearing in `site_visits` by definition have no visits +1. – Tim Biegeleisen Nov 08 '17 at 08:09
I've mentioned in the question (and the title) that I cannot use a subquery in FROM, since it's not supported by ORM. I got a working solution with a subquery in FROM. – Random42 Nov 08 '17 at 08:34
@m3th0dman JPA supports subqueries and this is the correct answer in my mind. – Tim Biegeleisen Nov 08 '17 at 08:41
@m3th0dman Is the subquery in join like a derived table acceptable? – Kapil Nov 08 '17 at 08:44
select count(distinct st.site_id) from site_visit st inner join (select site_id from site_visit group by site_id having sum(visits)>50) t on st.site_id=t.site_id – Kapil Nov 08 '17 at 08:46
@TimBiegeleisen Are you sure JPA supports subqueries in FROM? according to these answers, it doesn't https://stackoverflow.com/a/10536155/1291238 and https://stackoverflow.com/a/11574954/1291238 As I saw JPA accepts sub-selects only in WHERE and HAVING; thus not in JOIN or FROM. – Random42 Nov 08 '17 at 09:15
@m3th0dman Why don't you just run the above group by query and then check the size of the result set in your Java code? – Tim Biegeleisen Nov 08 '17 at 09:17
@TimBiegeleisen That's the only solution I have thus far; but the result might be large and I wanted to make sure there isn't another solution which would return the exact number from SQL. – Random42 Nov 08 '17 at 09:22

score 0 · Answer 3 · answered Nov 08 '17 at 09:20

0

How about this:

select count(1) from
(select a.site_id,sum(visits) as visits_sum  from site_visits a,sites b where 
a.site_id = b.site_id group by a.site_id having sum(visits)>50) as ab

answered Nov 08 '17 at 09:20

Vijunav Vastivch

4,153
1
16
30

It's written both in title and in question description that the solution is needed without a sub-query in FROM (due to ORM restrictions); a solution with sub-query in FROM is already posted in the question details. – Random42 Nov 08 '17 at 09:23
There is another way to avoid a sub-query @m3th0dman, Do it in function. – Vijunav Vastivch Nov 08 '17 at 09:58

Write SQL count with HAVING sum restrictions in a JOIN without a sub-query in FROM

3 Answers3