CODEWITHSUNDEEP
sqlsql-server
Hammelkeule
Hammelkeule

Reputation: 317

SQL Server : select results where the total is equal a specific value

I have a select which returns the following output:

Percent Sales  |   Brand
---------------+-------------
30.12              BMW
29.11              Honda
22.12              Daimler
06.90              Mercedes
02.11              VW
00.12              Ford

The output should be the brands, which are making the top 80 percent income.

So in this case it would be the first 3 rows.

Upvotes: 0

Views: 62

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93744

Considering there is a Id column to order the results. You need to find running total

;WITH cte
     AS (SELECT *,
                Sum([Percent Sales])OVER(ORDER BY id) AS run_tot
         FROM   Yourtable)
SELECT *
FROM   (SELECT *,
               Min(CASE WHEN run_tot > 80 THEN run_tot END) OVER() min_run_tot
        FROM   cte) a
WHERE  run_tot <= min_run_tot
ORDER  BY id

or as mentioned in comments if you want to order the result in [Percent Sales] desc then 

;WITH cte
     AS (SELECT *,
                Sum([Percent Sales])OVER(ORDER BY [Percent Sales] desc) AS run_tot
         FROM   Yourtable)
SELECT *
FROM   (SELECT *,
               Min(CASE WHEN run_tot > 80 THEN run_tot END) OVER() min_run_tot
        FROM   cte) a
WHERE  run_tot <= min_run_tot
ORDER  BY id

Upvotes: 3

Related Questions