I wrote a simple query to find the percentage of two columns but I will like the result in a column with only two decimal places. This is what I wrote but I am getting error and I can't seem to be able to fix it.

SELECT 
    Small_Bags,
    Total_Bags,
    (Small_Bags / Total_Bags) * 100 AS Small_Bags_Percent
    (Select CAST (Small_Bags_Percent AS DECIMAL (10,2)))
FROM Table_Bags
WHERE Total_Bags <>0
 
 

CodePudding user response：

You cannot use your column alias on same query. You can do this.

select Small_Bags
    , Total_Bags
    , (Small_Bags / Total_Bags) * 100 AS Small_Bags_Percent
    , cast(((Small_Bags / Total_Bags) * 100) as decimal(10,2))
from Table_Bags
where Total_Bags > 0

or use subquery

select t1.Small_Bags, t1.Total_Bags, t1.Small_Bags_Percent
    , cast(t1.Small_Bags_Percent as decimal(10,2))
from 
    (select Small_Bags
        , Total_Bags
        , (Small_Bags / Total_Bags) * 100 AS Small_Bags_Percent
        , cast(((Small_Bags / Total_Bags) * 100) as decimal(10,2))
    from Table_Bags
    where Total_Bags > 0) t1

CodePudding user response：

If this is MySQL, I can see about 3 possible cause of error from the query.

1. There's no comma after Small_Bags_Percent.
2. CAST ( ... shouldn't have a space in between the opening parenthesis so it suppose to be CAST( ....
3. Wrong way of selecting Small_Bags_Percent. It's possible when doing it like so:

SELECT 
  Small_Bags,
  Total_Bags,
  (Small_Bags / Total_Bags) * 100 AS Small_Bags_Percent,
  CAST(((SELECT Small_Bags_Percent)) AS DECIMAL (10,2))
FROM Table_Bags
WHERE Total_Bags <>0

But, if you have no reason to have two Small_Bags_Percent columns, why don't just directly CAST() onto the first one?

SELECT 
  Small_Bags,
  Total_Bags,
  CAST((Small_Bags / Total_Bags) * 100 AS DECIMAL (10,2)) AS Small_Bags_Percent
FROM Table_Bags
WHERE Total_Bags <>0