How to display a specific range of numbers in an SQL query

So there's just a little bit more to this than the title suggests. I've got a table which contains a name column and a qty column, I want to display the results who have a TOTAL sum qty between 500 and 1500.

Example:

name     qty
------  ----
brad     100
cody     300
kylie   1100
brad     800
nelson  1200
bob      600
nelson   100
kylie    600

I want the result to display

name     qty
------  ----
brad     900
nelson  1300
bob      600

Hopefully that makes sense. This is the query I've got

SELECT name, SUM(qty) AS Total 
FROM t1 
    NATURAL JOIN t2 
WHERE qty BETWEEN 500 AND 1500 
GROUP BY name 
ORDER BY name

The issue is that it seems to only be summing the fields in qty that are between 500 and 1500, rather than display only the Total fields that are in that range. I tried doing "WHERE SUM(qty) BETWEEN...." but this results in a compilation error (i'm using SQLite studio)

This is a homework problem for a database class I'm in, I'm looking to learn, not just get the answer. Thanks!

Solution

There is no need for the join and you need to add a HAVING clause

SELECT name, SUM(qty) AS Total 
FROM t1 
GROUP BY name 
HAVING SUM(qty) BETWEEN 500 AND 1500
ORDER BY name