Reputation: 291
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
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
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