Percentage in a group in sql

,
1

Hi,

May anyone please share your expertise for beow complicated scenario.

DDL:

DECLARE @MYTABLE TABLE
(
PAN INT,
DELUXPRICE NUMERIC(10,5),
[SUPER DELUX] NUMERIC(10,5),
SHUTTLENO INT,
ROUTECOUNTRY CHAR(10),
REMARKS VARCHAR (15)
)

INSERT @MYTABLE

SELECT 20, 7.0600, 5.8833333, 101, 'USA', 'COSTLY' UNION ALL
SELECT 20, 6.3000, 5.2500000, 101, 'USA', 'COSTLY' UNION ALL
SELECT 40, 17.0000, 14.1666666, 101, 'USA', 'CHEAPER' UNION ALL
SELECT 20, 12.3500, 10.2916666, 102, 'USA', 'COSTLY' UNION ALL
SELECT 20, 28.4800, 23.7333333, 103, 'USA', 'CHEAPER' UNION ALL
SELECT 20, 12.6000, 10.5000000, 103, 'USA', 'COSTLY' UNION ALL
SELECT 25, 10.0800, 8.4000000, 103, 'USA', 'COSTLY' UNION ALL
SELECT 20, 11.9600, 9.9666666, 103, 'USA', 'CHEAPER' UNION ALL
SELECT 20, 16.3100, 13.5916666, 103, 'USA', 'COSTLY' UNION ALL
SELECT 20, 11.0900, 9.2416666, 103, 'USA', 'COSTLY'

I have to calculate the percentage of costley and cheaper in a group of shuttleno

for example OUTPUT FOR SHUTTLENO 101 group

SHUTTLENO,ROUTECOUNTRY,COSTLYPERCENTAGE,CHEAPERPERCENTAGE
101,USA,66.33%,44.77%

here COSTLYPERCENTAGE i calculated as sum(count of costly)/sum(total count)*100
similrly calculated for CHEAPERPERCENTAGE

Thanks

2

Something like this, perhaps?

select SHUTTLENO
     , ROUTECOUNTRY
	 , sum(case REMARKS when 'COSTLY' THEN 1.0 ELSE 0.0 END) over(partition by shuttleno)
	   /
	   count(*) over(partition by shuttleno)
	   AS COSTLYPERCENTAGE
	 , sum(case REMARKS when 'CHEAPER' THEN 1.0 ELSE 0.0 END) over(partition by shuttleno)
	   /
	   count(*) over(partition by shuttleno)
	   AS CHEAPERPERCENTAGE
from @MYTABLE