Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Calculate Mode in SQL

Posted on 2004-10-29
10
Medium Priority
?
926 Views
Last Modified: 2008-02-26
Hi Experts:
I have a situation where I need to Update the Mode. For your convience I have script for creating a table and for inserting data. I also wrote down the result I want. Basically I need to calculate the median.

CREATE TABLE [dbo].[Data] (
     [Product Number] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [Store Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [Price] [decimal](18, 0) NULL ,
     [Store_id] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO Data values   ('12', 55, 'I', 565 )
INSERT INTO Data values   ('12', 45, 'I', 566 )
INSERT INTO Data values   ('12', 64, 'I', 567 )
INSERT INTO Data values   ('12', 64, 'I', 577 )
INSERT INTO Data values   ('12', 60, 'I', 588 )
INSERT INTO Data values   ('12', 125, 'I', 587 )
INSERT INTO Data values   ('12', 38, 'I', 500 )
INSERT INTO Data values   ('12', 72, 'I', 545 )
INSERT INTO Data values   ('12', 45, 'I', 555 )
INSERT INTO Data values   ('12', 49, 'NC', 574 )
INSERT INTO Data values   ('12', 49, 'NC', 588 )
INSERT INTO Data values   ('12', 43, 'NC', 545 )
INSERT INTO Data values   ('12', 40, 'RC', 514 )


CREATE TABLE [dbo].[MMMfinal] (
[Product Number] [int] NULL ,
[Store Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AVG_Price] [float] NULL ,
[Median_Price] [float] NOT NULL ,
[Mode_Price] [float] NOT NULL
)
INSERT INTO MMMfinal values   ('12', 'I', 0,0,0 )
INSERT INTO MMMfinal values   ('12', 'NC', 0,0,0 )
INSERT INTO MMMfinal values   ('12', 'RC', 0,0,0 )

Need ONE UPDATE statement which will get me the follwoing and Second Option split the work into 2-3 scripts as it does not matter if I need to create temp tables.
12|I|0|0|54.50
12|NC|0|0|49
12|RC|0|0|40

Thanks in advance I know I will get this one very quickly.
0
Comment
Question by:dhamijap
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 12445543
The following query

select [Product Number], [Store Type], [Price],  count(*) as cnt
from [Data]
group by [Product Number], [Store Type], [Price]

finds out the number of occurences of a each price inside a [Product Number], [Store Type] group

--step 1 : insert it into a #temp table order by count(*) desc
select [Product Number], [Store Type], [Price], identity(1,1) as seqnum
into #temp
from [Data]
group by [Product Number], [Store Type], [Price]
order by count(*) desc  -- the mode price has the min seqnum

-- step 2 : now you can update the table
update a
set Mode_Price = b.Price
from MMMFinal a
inner join #temp b on a.[Product Number] = b.[Product Number] and a.[Store Type] = b.[Store Type]
inner join (select [Product Number], [Store Type], min(seqnum) as seqnum from #temp group by [Product Number], [Store Type]) c on b.[Product Number] = c.[Product Number] and b.[Store Type] = c.[Store Type] and b.seqnum = c.seqnum

-- step 3 : drop the temp table
drop table #temp

HTH

Hilaire
0
 
LVL 6

Expert Comment

by:pedros7
ID: 12445681
note that there can be several mode values for the same product/store type combination!!
e.g. For Prod 12, Store Type 'I', you've got 64 and 45! dhamijap was then averaging it..
0
 

Author Comment

by:dhamijap
ID: 12445736
yes Pedros7 is right I need the avg of those which are multiple as you said
dhamijap
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 26

Accepted Solution

by:
Hilaire earned 1000 total points
ID: 12470754
Another trial ....

-- step 1 : populate #temp table with count group by number, type, price
select [Product Number], [Store Type], [Price],  count(*) as cnt
into #temp
from [Data]
group by [Product Number], [Store Type], [Price]

/*
-- this query gets the mode for each group (for test only)
select x.[Product Number], x.[Store Type], AVG(x.[Price]) as Price
from #temp x
inner join (
   select [Product Number], [Store Type], MAX(cnt) as maxcnt
   from #temp
   group by [Product Number], [Store Type]
) y on x.[Product Number] = y.[Product Number] and x.[Store Type] = y.[Store Type] and x.cnt = y.maxcnt
*/

-- step 2 : use the query above as a subquery to update your table
update a
set Mode_Price = b.Price
from MMMFinal a
inner join (
select x.[Product Number], x.[Store Type], AVG(x.[Price]) as Price
      from #temp x
      inner join (
            select [Product Number], [Store Type], MAX(cnt) as maxcnt
            from #temp
            group by [Product Number], [Store Type]
      ) y on x.[Product Number] = y.[Product Number] and x.[Store Type] = y.[Store Type] and x.cnt = y.maxcnt
) b on a.[Product Number] = b.[Product Number] and a.[Store Type] = b.[Store Type]

-- step 3 : drop the temp table
drop table #temp
0
 
LVL 6

Expert Comment

by:pedros7
ID: 12471167
-- Minor corrections:
-- added Group by lines
-- minor change in MMMfinal table definition
-- delete all data in MMMFinal table before re-running query, old data is irrelevant
-- changed update to insert

-- Still needs adding median and average info


CREATE TABLE [dbo].[MMMfinal] (
    [Product Number] [int] NULL ,
    [Store Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AVG_Price] [float] NULL ,
    [Median_Price] [float] NULL ,
    [Mode_Price] [float] NULL
)


delete from MMMfinal

-- step 1 : populate #temp table with count group by number, type, price
select [Product Number], [Store Type], [Price],  count(*) as cnt
into #temp
from [Data]
group by [Product Number], [Store Type], [Price]

-- step 2 : use the query above as a subquery to update your table

delete from MMMFinal

Insert Into MMMFinal ([Product Number], [Store Type], [Mode_Price])
    select x.[Product Number], x.[Store Type], AVG(x.[Price]) as Price
     from #temp x
     inner join (
          select [Product Number], [Store Type], MAX(cnt) as maxcnt
          from #temp
          group by [Product Number], [Store Type]
     ) y on x.[Product Number] = y.[Product Number] and x.[Store Type] = y.[Store Type] and x.cnt = y.maxcnt
     Group by x.[Product Number], x.[Store Type]


-- step 3 : drop the temp table
drop table #temp

select * from mmmfinal
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12471199
@pedros7
I think you shouldn't delete from mmmfinal
The median and the mode need to be calculated separately I think.
0
 
LVL 6

Expert Comment

by:pedros7
ID: 12471242
the idea behind clearing the table is that: when i ran it first time, with mmmfinal empty, immmfinal would remain empty (as there where no values to update!!), when i looked at resolving that bit, i realised we're working with current data everytime the query is run, if a product/store combination isn't already being returned, it shouldn't be there?

:) you mean, the median and the average price needs calculating seperatly. yeah, agree, looking at running the last solution from the other answer, and using that table to update this one. what do you think?
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12473445
>>
:) you mean, the median and the average price needs calculating seperatly. yeah, agree, looking at running the last solution from the other answer, and using that table to update this one. what do you think?
<<
The median has been calculated in another question.
I assumed the mmmfinal table had been feed/updated using the previous answer, and that it just needed to be updated with the newly calculated mode.
Maybe I'm wrong ?

dahmijap, any comments from your side ?

0
 
LVL 6

Expert Comment

by:pedros7
ID: 12473679
right, get where you're coming from.. just the process needs setting up then
this morning been completely hectic! haven't been able to look at it anymore. also, going now on hols until sunday eve. prague awaits. :)
cheers
Pedro
0
 

Author Comment

by:dhamijap
ID: 12474257
yes, Mean and Median are calculated in the same proceedure but I have just not shown here. I got dragged into another item so I did not get to it I will resume testing shortly.
dhamijap
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

596 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