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

Mark Storey-Smith
  • 31,687
  • 8
  • 89
  • 124
Biju jose
  • 2,088
  • 2
  • 18
  • 27
  • 3
    Use group by sales.stor_id, stores.stor_name – ypercubeᵀᴹ Aug 09 '12 at 08:41
  • 2
    You have to group all columns treated in the projection or use aggregate functions – edze Aug 09 '12 at 08: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 09 '12 at 08:44
  • @ypercube thanks ypercube for the fast reply and it did worked for me thanks again – Biju jose Aug 09 '12 at 08: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 09 '12 at 08:57
  • @Bijujose: a fast search reveals a few links: sql-language-tutorial – ypercubeᵀᴹ Aug 09 '12 at 09:03
  • @mark storey smith thanks mark for editing .Next time i will keep in my mind to write the questions far better – Biju jose Aug 09 '12 at 09:03
  • @ypercube well i think the post i made for getting the tutorials is against the rule of stack exchange what should i do ypercube may i delete the post? – Biju jose Aug 09 '12 at 09:37
  • @Biju: it's a comment, no worries. You can always flag a comment of yours if you want (explaining that you think it should be removed) and a moderator will remove it. Also, if you want to ask something and you are not sure if it allowed or a good fit for a question, you can join the Heap chat room. There's almost always someone there. – ypercubeᵀᴹ Aug 09 '12 at 13:34
  • @Mark Storey-Smith thanks mark for the edit i have a little doubt ,how did you colored the elements in the query like the select ,from ,join all got a brown color how can i do that? – Biju jose Aug 10 '12 at 04:25

1 Answers1

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.

Shlomi Noach
  • 7,363
  • 1
  • 24
  • 24