Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
3  
Use group by sales.stor_id, stores.stor_name –  ypercube Aug 9 '12 at 8:41
2  
You have to group all columns treated in the projection or use aggregate functions –  edze Aug 9 '12 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? –  ypercube Aug 9 '12 at 8:44
    
@ypercube thanks ypercube for the fast reply and it did worked for me thanks again –  Biju jose Aug 9 '12 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 '12 at 8:57

1 Answer 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.

share|improve this answer
    
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 '12 at 9:46
1  
'sales.stor_id,' you need the comma –  edze Aug 9 '12 at 10:03
    
Thanks, Biju jose & edze, I have edited the text –  Shlomi Noach Aug 9 '12 at 10:31
    
now it worked shlomi noach thanks for extra info –  Biju jose Aug 9 '12 at 11:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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