Customers Contributing to 80% of Total Sales Turnover

Transact-SQL
1

Dear Experts,

I have a list of customers with their respective sales. there are multiple sales for few customers.

The scripted form of table data is as follows:-

CREATE TABLE mytable (
Cust VARCHAR(1) NOT NULL PRIMARY KEY
,Sales INT NOT NULL
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'A'
,100
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'B'
,300
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'C'
,500
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'D'
,1000
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'E'
,100
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'F'
,50
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'G'
,300
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'I'
,400
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'G'
,500
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'A'
,600
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'E'
,700
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'G'
,800
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'I'
,800
);

I need to have list of customers who are contributing to 80% of the total sales. In above data the total sales is Rs. 6150. 80% of 6150 is Rs. 4920/-. I need only those records (after totalling sales amount customerwise and sorting the records on salesamount in descending order) of the customers who are contributing to 80% of total sales turnover. That means the records in which only totalling of sales is <= 4920/-

Following is the output needed as per above requriement.

Output

+------+-------+----------------------------------+
| Cust | Sales | 80% Sales Contributing Customers |
+------+-------+----------------------------------+
| G | 1600 | 1600 |
| I | 1200 | 2800 |
| D | 1000 | 3800 |
| E | 800 | 4600 |
+------+-------+----------------------------------+
Thanks for the help in advance

2

You can write this , in different ways ; here is one, written in "stages" , to be more clear.

;WITH Total AS
(
SELECT 
    SUM(Sales)  AS TotalSales
    ,SUM(Sales) * 0.80 AS TotalSales_80
FROM myTable AS T
)
,TotalCust AS
(
SELECT Cust 
    ,SUM(Sales) AS TotalSales_Cust  
FROM myTable as T
GROUP BY Cust
) 
,TotalCust_Contrib AS
(
SELECT Cust 
    ,TotalSales_Cust    
    ,SUM(TotalSales_Cust) OVER(ORDER BY TotalSales_Cust DESC) AS TotalSales_Contribution
FROM TotalCust as T
)

SELECT Cust 
    ,TotalSales_Cust
    ,TotalSales_Contribution
FROM 
  TotalCust_Contrib
WHERE
 TotalSales_Contribution < (Select TotalSales_80 FROM Total)
Cust TotalSales_Cust TotalSales_Contribution
G 1600 1600
I 1200 2800
D 1000 3800
E 800 4600

dbfiddle

3

hi

i know this topic was over 7 months ago

i have tried a different approach using recursive cte

"seniors" bitsmed and Scott Pletcher have mentioned
that recursive CTEs have performance issues

Since this is a very small amount of data
:slight_smile:
:slight_smile:

drop create data .. 
use tempdb 
go 

drop table mytable 
go 

CREATE TABLE mytable (
Cust VARCHAR(1) NOT NULL 
,Sales INT NOT NULL
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'A'
,100
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'B'
,300
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'C'
,500
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'D'
,1000
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'E'
,100
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'F'
,50
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'G'
,300
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'I'
,400
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'G'
,500
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'A'
,600
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'E'
,700
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'G'
,800
);

INSERT INTO mytable (
Cust
,Sales
)
VALUES (
'I'
,800
);
SQL using recursive CTE 
;WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY Sum(sales) DESC ) AS rn, 
                cust, 
                Sum(sales)                     sumsales 
         FROM   mytable 
         GROUP  BY cust), 
     reccte 
     AS (SELECT rn, 
                cust, 
                sumsales 
         FROM   cte 
         WHERE  rn = 1 
         UNION ALL 
         SELECT a.rn, 
                a.cust, 
                a.sumsales + b.sumsales 
         FROM   cte a 
                JOIN reccte b 
                  ON a.rn = b.rn + 1 
         WHERE  a.sumsales + b.sumsales <= 4920) 
SELECT * 
FROM   reccte 

go
Results

image