2

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!

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
dks209
  • 57
  • 11

2 Answers2

5

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
Adish
  • 709
  • 4
  • 12
0

You need to use the HAVING clause, which is evaluated after aggregate functions like SUM:

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

I'd also recommend against NATURAL JOIN. It's generally considered bad practice because it introduces a point of error for no good return.

Community
  • 1
  • 1
Bacon Bits
  • 30,782
  • 5
  • 59
  • 66
  • t1 contains the **name** column and an **id** column, and t2 has the **qty** column and **id** column. The id is the commonality that links the qty to a specific name. Without doing a NATURAL JOIN, what would be the better way for me to do it? – dks209 Nov 03 '15 at 03:38
  • 1
    @dks209 Explicitly specify the JOIN condition: `FROM t1 NATURAL JOIN t2` becomes `FROM t1 INNER JOIN t2 ON t1.id = t2.id`. – Bacon Bits Nov 03 '15 at 03:43