Calculate Mode in SQL

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.
dhamijapAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HilaireCommented:
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
pedros7Commented:
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
dhamijapAuthor Commented:
yes Pedros7 is right I need the avg of those which are multiple as you said
dhamijap
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HilaireCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pedros7Commented:
-- 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
HilaireCommented:
@pedros7
I think you shouldn't delete from mmmfinal
The median and the mode need to be calculated separately I think.
0
pedros7Commented:
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
HilaireCommented:
>>
:) 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
pedros7Commented:
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
dhamijapAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.