top n and sum(filed)

[sean sobey]

Hello everyone,
I am trying to to get the top 10 sold amount for various products by a sales
person in tha past 40 days . So I write something like this:

select top ten product, sum(soldamount), sum(orderedQuantity), salesman
where salesman = 'Joe Blow' and (select getdate()) - 40
group by product, soldamount, orderedQuanitity, salesman
order by product

This does not give me what I want. The numbers returned are not accurate at
all. Am I missing something?

Thank you for putting me out of my misery,

 

[Kevin Spencer]

In SQL, "TOP N" means the first N records in the result set. That's all it
means. When you add an ORDER BY clause, the records are first ordered, and
the top N records in the result set are returned. So, when you ORDER BY
product, you will get the first 10 products alphabetically. to get the
products with the most "soldamount" you would ORDER BY that field. Of
course, this doesn't make your products come out alphabetically. To do that,
you would have to use a subquery. The subquery gets the TOP 10 records by
"soldamount" and the outer query selects the results from the inner query
and orders them by product.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

 

[sean sobey]

Thank you Kevin for responding and making things clear. Additionally, I
discovered that I should do something like this: sum(soldamount) as
TotalAmount
and then order by TotalAmount.
Sean

 

[Kevin Spencer]

Oh yeah, sorry I forgot to mention the need for aliasing when doing an
aggregate query. Aliasing is also important when doing a JOIN query, or any
type of query that contains derived tables in it.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.