如何在SQL查询中显示特定范围的数字

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.

所以这比标题所暗示的要多一点。我有一个包含名称列和数列的表，我想显示总计数量在500到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)

问题是它似乎只是将qty中的字段汇总在500到1500之间，而不是仅显示该范围内的Total字段。我尝试做“WHERE SUM（qty）BETWEEN ....”但这会导致编译错误（我正在使用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!

这是我所在的数据库课的作业问题，我正在学习，而不仅仅是得到答案。谢谢！

2 个解决方案

#1

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

不需要连接，您需要添加HAVING子句

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

2 个解决方案

#1

更多相关文章

随机推荐