1

I am using the pub database in SQL Server 2005. Which stores have at least 5 orders? Should diplay the store name and store id. I have come up with a query something like this:

select 
    stores.stor_name
    , sales.stor_id  
from 
    sales 
join 
    stores 
on  sales.stor_id =stores.stor_id 
group by 
    sales.stor_id  
having 
    (count(sales.stor_id)>=5)

When answering please mention what i have done wrong

10
  • 3
    Use group by sales.stor_id, stores.stor_name Aug 9, 2012 at 8:41
  • 2
    You have to group all columns treated in the projection or use aggregate functions
    – edze
    Aug 9, 2012 at 8:42
  • Exactly. Or use a subquery (where you group by) and then join, as in this answer: Why do wildcards in GROUP BY statements not work? Aug 9, 2012 at 8:44
  • @ypercube thanks ypercube for the fast reply and it did worked for me thanks again
    – Biju jose
    Aug 9, 2012 at 8:51
  • @edze thanks for your opinion well edze can you please help me to provide some tips to master the sql with some tutorial in internet and also books to refer because i often make small mistakes like these even though i know all the keywords(not all though ) but dont have a proper idea to use them please help to make a concrete foundation on sql
    – Biju jose
    Aug 9, 2012 at 8:57

1 Answer 1

1

ypercube already gave the right answer, but here's another alternative:

Instead of GROUPing by all columns (leading to a bloated GROUP BY this, that, that, and_that_too), the following can be used:

select 
    sales.stor_id,  
    MIN(stores.stor_name) AS stor_name
from 
    sales 
join 
    stores 
on  sales.stor_id =stores.stor_id 
group by 
    sales.stor_id  
having 
    (count(sales.stor_id)>=5)

Though I confess we just move the bloating around... But this at least clarifies the mechanism of the query, by simplifying the GROUP BY.

3
  • when i used your query it just show an error like "incorrect syntax near stores" i think the MIN function you used is not the correct one to use please check your query
    – Biju jose
    Aug 9, 2012 at 9:46
  • 1
    'sales.stor_id,' you need the comma
    – edze
    Aug 9, 2012 at 10:03
  • now it worked shlomi noach thanks for extra info
    – Biju jose
    Aug 9, 2012 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.