Summary Report for Online and Offline sales in Percentage

We have a requirement to show the report of online and offline sales in percentage and count based on the territory.

For the example here we are taking the “AdventureWorks2017” database.

Below is the sql script to get the report.

SELECT st.NAME,        TotalOrders = Count (*),        Online_Sales_Pct = CONVERT (VARCHAR (50), Round ( (CONVERT (FLOAT, Sum (                           CASE                           WHEN                           sl.onlineorderflag = 1 THEN 1 ELSE 0 END)) / Count (*)                           ) * 100                           , 0) ) + '%',        Offline_Sales_Pct = CONVERT (VARCHAR (50), Round ( (CONVERT (FLOAT, Sum (                            CASE                            WHEN                            sl.onlineorderflag = 0 THEN 1 ELSE 0 END)) / Count (*                            )) *                            100, 0) ) + '%' FROM   sales.salesorderheader sl        JOIN sales.salesterritory st          ON sl.territoryid = st.territoryid GROUP  BY st.NAME ORDER  BY st.NAME

 Running this script.

Getting the below result.