Need help with SQL aggregation query

Question

What is the most straightforward approach to producing a data set that can be used in a SQL Server Reporting Services report to display the following:

SalesPerson        # Sales        # Gross        Profit
John Doe               100       $140,000       $25,000
Everyone Else (Avg.)  1200     $2,000,000      $250,000


Jane Smith              80       $100,000       $15,000
Everyone Else (Avg.)  1220     $2,040,000      $260,000


...and so on.

This is a very, very simplified example of what I'm trying to do (for instance, the real scenario involves showing the 'Everyone Else' broken out into three categorical rows), but it illustrates the primary objective of displaying aggregate data for each individual with a comparison to everyone else (exclusive). Pseudo-code would be fine. My first stab at the SQL code for this got pretty tangled pretty quickly, and I know there must be a more direct method.

Any tips appreciated.

Answer 1

Almost certainly not very performant, but declaratively clear:

declare @i int = 0
declare @j int = 1

select * from
(
select (@i = @i + 2) as order_col, SalesPerson, sales, gross, profit
from myTable order by SalesPerson

union all

select (@j = @j + 2) as order_col, 'Everybody else'
, (select sum(sales) from myTable i where i.SalesPerson <> o.Salesperson)
, (select sum(gross) from myTable i where i.SalesPerson <> o.Salesperson)
, (select sum(profit) from myTable i where i.SalesPerson <> o.Salesperson)
from myTable o
order by SalesPerson
) x order by order_col

(The second part of the UNION can definitely be improved, but it's late and I can't think straight..)

Answer 2

In SSRS, put an extra detail row in your table. Then use the Scope parameter on aggregate functions, and do the average from first principles.

Eg:

(Sum(Fields!Sales.Value, "table1") - Fields!Sales.Value) 
/
(Sum(Fields!NumSales.Value, "table1") - Fields!NumSales.Value)

Answer 3

If you do not mind formatting later, then if we assume you have something like:

First I'll need some helper variables for total counts

/* Few helper variables*/
DECLARE @TotalQuantity int
       ,@TotalAmount decimal(19, 4)
       ,@TotalProfit decimal(19, 4)
       ,@EveryoneElse int

Then we fetch total for everyone in a given period (YEAR = 2009)

/* Fetch totals in the period*/
SELECT  @TotalQuantity = sum(SalesQuantity)
       ,@TotalAmount = sum(SalesAmount)
       ,@TotalProfit = sum(Profit)
       ,@EveryoneElse = count(DISTINCT SalesPersonKey) - 1
FROM   factSales AS s
       JOIN dimDate AS d ON s.DateKey = d.DateKey
WHERE   [Year] = 2009

/* Now we have totals for everyone in the period */

And now for each person vs everyone else, but all in one row.

/* Totals for each sales person vs everyone else Average */
SELECT  FullName
       ,SUM(SalesQuantity) AS [PersonSalesCount]
       ,SUM(SalesAmount) AS [PersonSalesAmount]
       ,SUM(Profit) AS [PersonSalesProfit]
       ,( @TotalQuantity - SUM(SalesQuantity) ) / @EveryoneElse AS [EveryoneElseAvgSalesCount]
       ,( @TotalAmount - SUM(SalesAmount) ) / @EveryoneElse AS [EveryoneElseAvgSalesAmount]
       ,( @TotalProfit - SUM(Profit) ) / @EveryoneElse AS [EveryoneElseAvgSalesProfit]
FROM    factSales AS s
        JOIN dimDate AS d ON s.DateKey = d.DateKey
        RIGHT JOIN dimSalesPerson AS p ON p.SalesPersonKey = s.SalesPersonKey
WHERE   [Year] = 2009
GROUP BY FullName

Now you can package all this in a stored procedure with parameter(s) for date interval. May still need to tweak number of sales people to determine which were active in a certain period and how to count those who did not sell anything. With this, EveryoneElse means number of sales people who sold something -1; so if you have 10 sales people and only 5 sold something, than EveryoneElse = 4.

Answer 4

I am making some assumptions here but if you have a table like so

If object_id('Sales') is not null 
  Drop table Sales

CREATE TABLE [dbo].[Sales]
(
 [Salesperson] [nvarchar](50) NULL,
 [Sales] [int] NULL,
 [Gross] [money] NULL,
 [Profit] [money] NULL,
)

That is populated with Data like so

Insert into Sales values ('John Doe', 100, 200.00, 100.00)
Insert into Sales values ('John Doe', 125, 300.00, 100.00)
Insert into Sales values ('Jane Smith', 100, 200.00, 100.00)
Insert into Sales values ('Jane Smith', 125, 1.00, 0.50)
Insert into Sales values ('Joel Spolsky', 100, 2.00, 1.00)
Insert into Sales values ('Joel Spolsky', 125, 3.00, 1.00)

Then a stored procedure like so may give you what you are looking for

If object_id('usp_SalesReport') is not null 
Drop procedure usp_SalesReport

Go


Create Procedure usp_SalesReport
as
Declare @results as table
(
 SalesPerson nvarchar(50),
 Sales int,
 Gross money,
 Profit money
)

Declare  @SalesPerson nvarchar(50)
Declare SalesSums CURSOR FOR

Select  distinct SalesPerson from Sales

Open SalesSums

Fetch SalesSums INTO @SalesPerson

While @@Fetch_Status = 0

Begin
 Insert into @results Select Sales.Salesperson, sum(sales), sum(Gross), sum(profit) from Sales group by Sales.Salesperson having Sales.Salesperson = @SalesPerson
 Insert into @results Select 'EveryoneElse', avg(sales), avg(Gross), avg(profit) from Sales where Salesperson <> @SalesPerson

Fetch SalesSums INTO @SalesPerson          
End
Select * from @results
Close SalesSums
Deallocate SalesSums
Return