Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Sql Server - Calculate Mode for each product

Experts,

I have a table of product id's and prices at which the products sold.
ProductID
Price

Some products are listed in the table multiple times, up to hundreds or thousands of times depending on how many times the product was sold.

I want to find the Mode for each product.

I have this code:

SELECT TOP 1 with ties DATA
FROM   @Temp
WHERE  DATA IS Not NULL
GROUP  BY DATA
ORDER  BY COUNT(*) DESC

found here: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/calculating-mean-median-and-mode-with-sq/


But that only works to calculate for 1 product whereas I need to calculate it for each productid (I have over 1,000 productids in the table)

What is the best way to do this?
I am using SQL Server 2012

Thanks in advance.
0
bobinorlando
Asked:
bobinorlando
1 Solution
 
AndyAinscowCommented:
As I suggested in the other (identical) question:
SELECT DATA
FROM   @Temp
WHERE  DATA IS Not NULL
GROUP  BY DATA
ORDER  BY COUNT(*) DESC
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now