CODEWITHSUNDEEP
mysqlsql
faramir
faramir

Reputation: 291

Get number of entries from table in specified range

I'm trying to get number of entries that are greater than some values in other table using MySQL.

I have tables that are similar to these one (but a way bigger): 

CREATE TABLE test_ranges AS SELECT 'class 1' AS category, 'A' AS grade, 90 AS points;
INSERT INTO test_ranges VALUES ('class 1', 'B', 75),('class 2', 'A', 80);

CREATE TABLE test_results AS SELECT 'class 1' AS category, 100 AS points;
INSERT INTO test_results VALUES ('class 1', 95),('class 1', 94),('class 1', 85),('class 1', 55),('class 1', 44),('class 1', 33);

And there is the content of tables:

SELECT * FROM test_ranges;

+----------+-------+--------+
| category | grade | points |
+----------+-------+--------+
| class 1  | A     |     90 |
| class 1  | B     |     75 |
| class 2  | A     |     80 |
+----------+-------+--------+

And for results:

SELECT * FROM test_results;
+----------+--------+
| category | points |
+----------+--------+
| class 1  |    100 |
| class 1  |     95 |
| class 1  |     94 |
| class 1  |     85 |
| class 1  |     55 |
| class 1  |     44 |
| class 1  |     33 |
+----------+--------+

I would like to have number of entries for every category for every grade type.

When I try to select using LEFT JOIN it works almost like I want to, but the count is not correct.

SELECT test_ranges.category,grade,count(*) FROM test_ranges
  LEFT JOIN test_results USING(category)
  GROUP BY category,grade;

+----------+-------+----------+
| category | grade | count(*) |
+----------+-------+----------+
| class 1  | A     |        7 |
| class 1  | B     |        7 |
| class 2  | A     |        1 |
+----------+-------+----------+

When I add WHERE clause, I've got right count values, but not every category and grade:

SELECT test_ranges.category,grade,COUNT(*) FROM test_ranges
  LEFT JOIN test_results USING(category)
  WHERE test_results.points>=test_ranges.points
  GROUP BY category,grade;

+----------+-------+----------+
| category | grade | count(*) |
+----------+-------+----------+
| class 1  | A     |        3 |
| class 1  | B     |        4 |
+----------+-------+----------+

I would like to have results similar to:

+----------+-------+----------+
| category | grade | count(*) |
+----------+-------+----------+
| class 1  | A     |        3 |
| class 1  | B     |        4 |
| class 2  | A     |        0 |
+----------+-------+----------+

EDIT

I have found that:

SELECT test_ranges.category,grade,COUNT(test_results.points) FROM test_ranges
  LEFT JOIN test_results USING(category)
  WHERE test_results.points>=test_ranges.points OR test_results.points IS NULL
  GROUP BY category,grade;

returns the right thing. The problem here is only performance. In my original problem, the test_ranges is (query join query) statement that in result gives about 32 rows and test_results has 300k rows.

Upvotes: 0

Views: 96

Answers (2)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Use the on clause:

select rg.category, rg.grade, ifnull(count(rs.points),0) as num
  from test_ranges rg
  left join test_results rs
       on rg.category = rs.category
      and rs.points >= rg.points
 group by rg.category, rg.grade;

Fiddle: http://sqlfiddle.com/#!9/d503a/4/0

As noted by Gordon your expected result is a little odd though. This shows everyone who scores an A as also scoring a B since they meet that threshold. I'm not sure if that's what you actually want. I guess you could picture it as "the number of people who at least scored a grade of X..." and might be useful in that regard. In any case, just be aware.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Start with test_ranges and use a correlated subquery:

select tr.category, tr.grade,
       (select count(*)
        from test_results tr2
        where tr2.category = tr.category and
              tr2.points >= tr.points
       ) as cnt
from test_ranges tr;

This should actually return what your query returns.

Upvotes: 1

Related Questions