Aggregates and subqueries

In the pubs database I need to find all the books whose total sales (qty)
exceed the average total sales
I came up with this query...
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is there a better way to write this?
Hi David
Why are you JOINING with the titles table? There is nothing in that table
you are using. Sales has a title_id. You would only have to JOIN to titles
if you wanted the book title.
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David F" <davef@.nksj.ru> wrote in message
news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
> In the pubs database I need to find all the books whose total sales (qty)
> exceed the average total sales
> I came up with this query...
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> Is there a better way to write this?
>
|||Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>
|||Not quite. Check your GROUP BY:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David F" <davef@.nksj.ru> wrote in message news:OzHtRagGEHA.3816@.TK2MSFTNGP12.phx.gbl...
Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>