Results 1 to 2 of 2

Thread: % of total count as column

  1. #1
    Join Date
    Aug 2007
    Posts
    4

    % of total count as column

    Hi all,
    I need to create a computed column that counts values within a group and divides this by the total count for all groups to give a percentage of the total count.

    e.g.

    Sample data
    Clientid Sales
    ------- -----
    1 3
    1 1
    2 2
    2 3
    2 1
    3 2
    3 1
    4 5
    4 2

    The output I need is:
    Client_id Total Count ClientCount %of total
    -------- ----------- ----------- ---------
    1 20 4 20
    2 20 6 30
    3 20 3 15
    4 20 7 35

    I'm using SQL Server 2005. Can anyone help?

    Cheers

  2. #2
    Join Date
    Aug 2007
    Posts
    3
    there are always more than one solution but since you are using SQL 2005 you could use the new over() clause like:

    select distinct clientid, sum(sales) over() totalSales,
    sum(sales) over(partition by clientid) clientTotalSales,
    (sum(sales) over(partition by clientid) / sum(sales) over() ) * 100.0 percentOfTotal
    from salesData

    or use CTEs

    with totalSales(total)as
    (
    select sum(sales) from salesData
    )
    select clientid, total, sum(sales), sum(sales) / total * 100.0
    from salesData
    cross join totalsales
    group by clientid, total

Posting Permissions

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