CODEWITHSUNDEEP
sql-serversql-server-2005aggregate-functions
user2294664
user2294664

Reputation: 13

Calculate percentages on row groups

I'm sorry I couldn't find a better Title for this question, English is not my native language! I need to come up with this output:

SalesRep | Year | Month | Country | Call % 
--------   ----   -----   -------   ------
Name1      2012       1  USA          10.1 
Name1      2012       1  UK           12.6 
Name1      2012      12  USA          15.7 
Name1      2012      12  France       38.0 
Name1      2013       1  Spain        11.5      
Name1      2013       1  Angola       23.2
Name1      2013      12  Norway       20.1 
Name1      2013      12  Italy        20.9 
Name2      2012       1  Spain         9.4

SORRY!!! I forgot to mention that the percentages are calculated on total calls made by each sales rep. So, basically I need a percentage breakdown of calls made by each salesrep by country. So the basis for calculation is not the total number of calls made by all salesreps but the number of calls made by each salesrep.

I have a query which returns the Call counts but I cannot get the Call percentages to work:

set dateformat dmy
select u.first_name + ' ' + u.last_name as SalesRep, year(act.date_start) as Year, 
    month(act.date_start) as Month, acc.billing_address_country as Country, Count(*)
from accounts acc
    inner join activities act
        on acc.id = act.parent_id
    inner join users u
        on act.assigned_user_id = u.id
where act.deleted = 0 and acc.deleted = 0 and act.project_id = 'some_id' 
    and act.parent_type = 'Account' and act.activity_type = 'Call'
    and act.status = 'Held' and act.date_start >='01-01-2012 00:00:01' 
    and act.date_start <='01-12-2013 00:00:01'
group by u.first_name + ' ' + u.last_name, year(act.date_start), month(act.date_start), acc.billing_address_country
order by u.first_name + ' ' + u.last_name, year(act.date_start), month(act.date_start)

Can you please help me?

Thank you in advance!

Upvotes: 1

Views: 177

Answers (2)

Anon
Anon

Reputation: 10908

Wrap the query and use windowing functions:.

WITH t AS (
    select u.first_name + ' ' + u.last_name as SalesRep, year(act.date_start) as Year, 
        month(act.date_start) as Month, acc.billing_address_country as Country, Count(*) [CallCount]
    from accounts acc
        inner join activities act
            on acc.id = act.parent_id
        inner join users u
            on act.assigned_user_id = u.id
    where act.deleted = 0 and acc.deleted = 0 and act.project_id = 'some_id' 
        and act.parent_type = 'Account' and act.activity_type = 'Call'
        and act.status = 'Held' and act.date_start >='01-01-2012 00:00:01' 
        and act.date_start <='01-12-2013 00:00:01'
    group by u.first_name + ' ' + u.last_name, year(act.date_start), month(act.date_start), acc.billing_address_country
)
SELECT
  [SaleRep],[Year],[Month],[Country]
  100.0*[CallCount] / SUM([CallCount]) OVER(PARTITION BY [SaleRep],[Year],[Month]) AS [Percentage]
FROM t

The SUM([CallCount]) OVER(PARTITION BY [SaleRep],[Year],[Month]) calculates a sub-total over multiple rows without using a GROUP BY. This lets you mix aggregates with non-aggregates in a calculation, which is perfect for percentages.

Upvotes: 2

Richard
Richard

Reputation: 30618

The first part of your problem is going to be getting the per-salesperson totals available in the query. I would do this using an extra join:

inner join (select act.assigned_user as user_id, count(*) as total 
            from activities 
            where -- ... replicate activity constraints
            group by) salesperson_totals on u.id = salesperson_totals.user_id

This means you'll now have the total number available, so you can just work out the percentage with normal aggregate functions - add this to your select query:

, 100 * COUNT(*) / salesperson_totals.total AS Percentage

You may also need to cast the COUNT(*) and salesperson_totals.total as float in order for the calculation to work.

Upvotes: 0

Related Questions