CODEWITHSUNDEEP
mysqlsql-server
user963091
user963091

Reputation: 357

MSSQL Group by Percentage

Im trying to find a way to group my results into 3 groups that are predefined 0-33%, 34-66% and 67-100%. The calculation is already being set as "Depletion". I want to group the top, middle and bottom of this column into the three different groups. Here is the query I'm using from multiple tables.

I assume I need to find the highest value in the SELECT first so that I can calculate the percentage, but I cant seem to find a way to do that. From there I dont know how to group the items based on percent.

SELECT     l.name AS Location, i.name AS ItemName, li.par, COUNT(*) AS Sales, li.par - COUNT(*) AS Depletion
FROM         shoppingcartdetail AS o INNER JOIN
                  item AS i ON i.id = o.itemid INNER JOIN
                  location AS l ON o.locationid = l.id INNER JOIN
                  location_item AS li ON i.id = li.itemid
WHERE     (CONVERT(datetime, o.created_date_time, 101) BETWEEN GETDATE() - l.service_interval AND GETDATE()) AND (o.locationid = 1)
GROUP BY i.barcode, i.name, l.name, li.par

Sample result

ItemName                 | avgshelflife | unitcost | par | Sales | Depletion
Dr. Pepper Bottle 20 oz  | 90           | 0.83     | 70  | 31    | 39
Mountain Dew Bottle 20 oz| NULL         |NULL      | 70  | 27    | 43
Coke Can 12 oz           | NULL         |NULL      | 30  | 22    | 8
M&M's Peanut 1.74 oz     | 90           | 0.60     | 66  | 14    | 52
Dr. Pepper Can 12 oz     | 180          | 0.38     | 30  | 13    | 17

Where M&M's Peanut 1.74 oz would by the 100% threshold with the highest Depletion of 52. Items would be grouped into the three groups with a new column added.

In this case: Y=%*52(Depletion of M&M's Peanut 1.74 oz)

Group0-30% is 0-17
Group31-66% is 18-34
Group66-100% is 35-52

Results

Group0-30% Coke Can 12 oz   
Group31-66% Dr. Pepper Can 12 oz
Group66-100% Dr. Pepper Bottle 20 oz, Mountain Dew Bottle 20 oz, M&M's Peanut 1.74 oz

Thanks in advance!

Upvotes: 0

Views: 86

Answers (3)

TheGameiswar
TheGameiswar

Reputation: 28900

You could also declare a function which calculates percent based on row number..below is the logic ,you may want to refine as per your needs..further point to note is some rows may say zero percent,but the rows returned are inline with top percent clause..

Orderid is a PK in my demo,you can change it a unique number or PK in your case

Alter function dbo.getpercent
(@orderid as int
)
returns int
as
begin

declare @pcnt int
;with cte
as
(
select row_number() over (order by orderid)  as rn,orderid
from sales.orders)
select @pcnt=(cast(rn as float)/830)*100 from cte 
where orderid=@orderid

return @pcnt
end

select dbo.getpercent(orderid) ,* from sales.orders
order by orderid

Upvotes: 0

Priyank_Vadi
Priyank_Vadi

Reputation: 1138

As I understand your question you want to display "Depletion" which is percentage that you need to display in group. If this so you can use like this.

select *,CASE 
    WHEN (Depletion < 34) THEN 
        'Group0-30%' + ItemName 
    ELSE 
        CASE 
            WHEN (Depletion < 67) THEN 
                'Group31-66%' + ItemName 
            ELSE 
                'Group66-100%' + ItemName 
        END
    END from (
        SELECT     l.name AS Location, i.name AS ItemName, li.par, COUNT(*) AS Sales, li.par - COUNT(*) AS Depletion
        FROM         shoppingcartdetail AS o INNER JOIN
                          item AS i ON i.id = o.itemid INNER JOIN
                          location AS l ON o.locationid = l.id INNER JOIN
                          location_item AS li ON i.id = li.itemid
        WHERE     (CONVERT(datetime, o.created_date_time, 101) BETWEEN GETDATE() - l.service_interval AND GETDATE()) AND (o.locationid = 1)
        GROUP BY i.barcode, i.name, l.name, li.par) t

If not like that then please provide with data for better understand.

Thanks,

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28900

this works(ugly though),the idea is to use top percent and then do the rest,i have used sales.orders table for demo purposes,but the idea remains the same..

step1: get all percentages into three temp tables,i have used three different temp tables due to identity col and laziness,you can do it with one

select top 33 percent * ,'37 pcnt' as 'pcnt'  into #temp1 from sales.orders order by orderdate desc

select top 67percent * ,'67 pcnt' as 'pcnt'  into #temp2 from sales.orders order by orderdate desc

select  * ,'100 pcnt' as 'pcnt'  into #temp3 from sales.orders

now next step is the place where we get data..

with cte
as
(
select * from #temp
union all
select * from
(
select * from #temp1 t1 where not exists(select 1 from #temp t2 where t2.orderid=t1.orderid) 
)c

)
select * from cte
union all
select * from
(
select * from #temp3 t3 where not exists(select 1 from cte where cte.orderid=t3.orderid)
)b

Output: enter image description here

Upvotes: 0

Related Questions