Solved

Calculate Mode in SQL

Posted on 2004-10-29
881 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
Question by:dhamijap
    10 Comments
     
    LVL 26

    Expert Comment

    by:Hilaire
    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
    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
    yes Pedros7 is right I need the avg of those which are multiple as you said
    dhamijap
    0
     
    LVL 26

    Accepted Solution

    by:
    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
    -- 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
    @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
    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
    >>
    :) 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
    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
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: MongoDB Object-Document Mapper for NodeJS

    NodeJS (JavaScript on the server) is awesome, but some developers get confused about NoSQL when it comes to working in Node with MongoDB (NoSQL database). Do you need a better explanation of how to use Node.js with MongoDB? The most popular choice is the Mongoose library.

    Introduction If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later (http://www.experts-exchange.com/A_192.html) by BrandonGalderisi (h…
    This article describes some very basic things about SQL Server filegroups.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    913 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now