Solved

Sql Server - Calculate Mode for each product

Posted on 2014-09-19
1
196 Views
Last Modified: 2014-10-10
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
Comment
Question by:bobinorlando
1 Comment
 
LVL 44

Accepted Solution

by:
AndyAinscow earned 500 total points
ID: 40333343
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question