CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    SQL Server COUNT query with unique values.

    This is done in Microsoft SQL Server 2008 R2.

    I'll start out with an example table.

    Code:
        Organization | MoneyAmount | MoneyAmountAvg
        
        ISD          | 500         | 
        ISD          | 500         | 
        ISD          | 500         | 
        QWE          | 250         | 
        ISD          | 500         | 
        QWE          | 250         | 
        OLP          | 800         | 
        ISD          | 500         |
    I need the MoneyAmountAvg column to have a value of MoneyAmount/(# of times that organization shows up)

    So for example, the MoneyAmountAvg column for the ISD rows would have a value of 100.

    QWE would have 125 for each row in the MoneyAmountAvg column and OLP would have a value of 800 since it is there only once.

    This is only an example table. The actual table is much bigger and has more organizations, but it has the same criteria. Some organizations have multiple rows, while others are there only once.

    I just need a way for it to count how many times each organization is listed when I use an update statement for that organization's MoneyAmountAvg column. Hard coding it for each organization is definitely not an option since they can change at any moment.

    Any help is appreciated.

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,396

    Re: SQL Server COUNT query with unique values.

    Why do you want to have this MoneyAmountAvg column in the same table?
    Why not to move it to another table or view? Or if it is much more complicated than in your example then create a stored procedure returning a user defined table type?
    Victor Nijegorodov

  3. #3
    Join Date
    Mar 2013
    Posts
    2

    Re: SQL Server COUNT query with unique values.

    Never mind, I found the answer.

    select organization, moneyamount,
    moneyamount / count(*) over (partition by organization)
    from t

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured