It orders the percentage value wrongly-CodePudding

I created my projects in SQL Server. I would like to order my records in percentage values in descending orders. I am not aware why in a few cases it does not work. First of all, I created TEMP Table to obtain the set of results. It works properly, because my records are ordered descending:

DROP TABLE IF EXISTS #FeelingProductive55
CREATE TABLE #FeelingProductive55 (
[Age] varchar(300),
[How important would you say feeling productive is to your happin] varchar(300),
[The Quantity of feeling productivity is your happinnes] NUMERIC NOT NULL
)
INSERT INTO #FeelingProductive55
    SELECT DISTINCT  [Age],[How important would you say feeling productive is to your happin], 
            CAST(count([How important would you say feeling productive is to your happin]) OVER (PARTITION BY [How important would you say feeling productive is to your happin]) AS NUMERIC) as [The Quantity of feeling productivity is your happinnes]
    FROM Project3.[dbo].[fProductivityData]
    WHERE [Age] = '55 '
    ORDER BY [The Quantity of feeling productivity is your happinnes] DESC

I received it: It works appropriately, because my records are ordered in descending orders.

Then, I would like to show my records in percentage values in descending orders. To do it, I used VARIABLE and make calculations:

DECLARE @Total4 float;
SET @Total4 = (SELECT SUM([The Quantity of feeling productivity is your happinnes]) as Total4 FROM #FeelingProductive55);
PRINT @Total4;
SELECT [Age],[How important would you say feeling productive is to your happin],
        LTRIM(CONCAT((CAST(CAST([The Quantity of feeling productivity is your happinnes] as float) / @Total4 as DECIMAL(32,2))*100), ' ','%')) as [The Percentage of feeling productivity is your happinnes]
FROM #FeelingProductive55
ORDER BY [The Percentage of feeling productivity is your happinnes] DESC

The result of my records is:

As you can see, the order of my records is inappropriate.

Would you be so kind as to tell me it fails to show my records in descending orders in percentage values? I would appreciate it if you could modify my code or share your advice to make it properly, because I tried to find good solutions in internet, but I cannot find anything about it.

CodePudding user response：

This is due to the concat of % value as a string. You can overwrite columns name and add order by on only percentage value column.

See sample.

-- Create temp table for temp data
CREATE TABLE #TempTable(
ID int,
Date datetime,
Name char(20),
Percentage decimal(18,2))
GO
-- Insert temp data
INSERT INTO #TempTable (ID, Date, Name,Percentage) values (1,getdate(),'ABC',10)
INSERT INTO #TempTable (ID, Date, Name,Percentage) values (2,getdate(),'QWERT',80)
INSERT INTO #TempTable (ID, Date, Name,Percentage) values (3,getdate(),'ASDF',70)
INSERT INTO #TempTable (ID, Date, Name,Percentage) values (4,getdate(),'ZXCVB',35)
INSERT INTO #TempTable (ID, Date, Name,Percentage) values (5,getdate(),'YTYT',40)
GO
-- Write selecct query to get data with order by result
SELECT ID, Date, Name,Result FROM (
SELECT ID, Date, Name,Percentage,LTRIM(CONCAT(Percentage,' %')) as Result 
FROM #TempTable
) Report  ORDER BY Percentage DESC
GO

-- drop temp table.
DROP TABLE #TempTable
GO

CodePudding user response：

You can use a window function for this, using the OVER clause.

Then sort by the original column, instead of the varchar percent column

SELECT
  [Age],
  [How important would you say feeling productive is to your happin],
  FORMAT(
     [The Quantity of feeling productivity is your happinnes] * 100.0
      / SUM([The Quantity of feeling productivity is your happinnes]) OVER (),
   'P2') as [The Percentage of feeling productivity is your happinnes]
FROM #FeelingProductive55
ORDER BY
  [The Quantity of feeling productivity is your happinnes] DESC