Percentage Of Sales For Each Product and Salesperson

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

My data records are like this:

Product Sales SalesPerson
ABC 10 Steve
ABC 1 Jim
ABC 3 Mike
ABC 4 Carol
ABC 8 Todd
ABC 9 Pat
DEF 22 Steve
DEF 11 Jim
DEF 7 Mike
DEF 6 Carol
DEF 90 Todd
DEF 21 Pat

I need a query that will show me the percentage each saleperson sold of each
product.

I am trying to get output like this:

Steve Jim Mike Carol Todd Pat
ABC 29% 3% 9% 11% 23% 26%
DEF 14% 7% 4% 4% 57% 13%


Thank you in advance.
 
Use two queries ---
carl_Crosstab_1 ----
SELECT carl.Product, Sum(carl.Sales) AS [Total Of Sales]
FROM carl
GROUP BY carl.Product;

TRANSFORM Sum([Sales]/[Total Of Sales]) AS Expr1
SELECT carl.Product
FROM carl INNER JOIN carl_Crosstab_1 ON carl.Product = carl_Crosstab_1.Product
GROUP BY carl.Product
PIVOT carl.SalesPerson;
 
Thanks Karl.

I have another variation.

My data records also contain the date.

For a given SalesPerson, I am trying to get the percentage sales for each
product for each date.

For example, for SalesPerson Steve, output would look something like this:


Steve
19-Aug 20-Aug
ABC 29% 22%
DEF 14% 10%


Is this possible ?

KARL DEWEY said:
Use two queries ---
carl_Crosstab_1 ----
SELECT carl.Product, Sum(carl.Sales) AS [Total Of Sales]
FROM carl
GROUP BY carl.Product;

TRANSFORM Sum([Sales]/[Total Of Sales]) AS Expr1
SELECT carl.Product
FROM carl INNER JOIN carl_Crosstab_1 ON carl.Product = carl_Crosstab_1.Product
GROUP BY carl.Product
PIVOT carl.SalesPerson;

--
KARL DEWEY
Build a little - Test a little


carl said:
My data records are like this:

Product Sales SalesPerson
ABC 10 Steve
ABC 1 Jim
ABC 3 Mike
ABC 4 Carol
ABC 8 Todd
ABC 9 Pat
DEF 22 Steve
DEF 11 Jim
DEF 7 Mike
DEF 6 Carol
DEF 90 Todd
DEF 21 Pat

I need a query that will show me the percentage each saleperson sold of each
product.

I am trying to get output like this:

Steve Jim Mike Carol Todd Pat
ABC 29% 3% 9% 11% 23% 26%
DEF 14% 7% 4% 4% 57% 13%


Thank you in advance.
Click to expand...
Click to expand...
 
Try this UNTESTED --
carl_Crosstab_1 ----
SELECT carl.Product, SalesDate, Sum(carl.Sales) AS [Total Of Sales]
FROM carl
GROUP BY carl.Product, carl.SalesDate;

TRANSFORM Sum([Sales]/[Total Of Sales]) AS Expr1
SELECT carl.SalesDate, carl.Product
FROM carl INNER JOIN carl_Crosstab_1 ON carl.Product = carl_Crosstab_1.Product
GROUP BY carl.SalesDate, carl.Product
PIVOT carl.SalesPerson;

TRANSFORM Sum([Sales]/[Total Of Sales]) AS Expr1
SELECT carl.SalesPerson, carl.Product
FROM carl INNER JOIN carl_Crosstab_1 ON carl.Product = carl_Crosstab_1.Product
GROUP BY carl.SalesPerson, carl.Product
PIVOT carl.SalesDate;

--
KARL DEWEY
Build a little - Test a little


carl said:
Thanks Karl.

I have another variation.

My data records also contain the date.

For a given SalesPerson, I am trying to get the percentage sales for each
product for each date.

For example, for SalesPerson Steve, output would look something like this:


Steve
19-Aug 20-Aug
ABC 29% 22%
DEF 14% 10%


Is this possible ?

KARL DEWEY said:
Use two queries ---
carl_Crosstab_1 ----
SELECT carl.Product, Sum(carl.Sales) AS [Total Of Sales]
FROM carl
GROUP BY carl.Product;

TRANSFORM Sum([Sales]/[Total Of Sales]) AS Expr1
SELECT carl.Product
FROM carl INNER JOIN carl_Crosstab_1 ON carl.Product = carl_Crosstab_1.Product
GROUP BY carl.Product
PIVOT carl.SalesPerson;

--
KARL DEWEY
Build a little - Test a little


carl said:
My data records are like this:

Product Sales SalesPerson
ABC 10 Steve
ABC 1 Jim
ABC 3 Mike
ABC 4 Carol
ABC 8 Todd
ABC 9 Pat
DEF 22 Steve
DEF 11 Jim
DEF 7 Mike
DEF 6 Carol
DEF 90 Todd
DEF 21 Pat

I need a query that will show me the percentage each saleperson sold of each
product.

I am trying to get output like this:

Steve Jim Mike Carol Todd Pat
ABC 29% 3% 9% 11% 23% 26%
DEF 14% 7% 4% 4% 57% 13%


Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top