CODEWITHSUNDEEP
sqlsql-server
Tauseef Hussain
Tauseef Hussain

Reputation: 1079

SQL: Reduce the size of result set

SELECT p.Distributor, SUM(r.SalesVolume) as Sales, 
((Cast(SUM(r.SalesVolume) as decimal(14, 4)) / (SELECT SUM(r.SalesVolume) FROM RawData r)) * 100) as MarketSharesVolume
FROM RawData r
INNER JOIN Product p
ON r.ProductId = p.ProductId
WHERE p.Distributor in ('TF1','WARNER')
GROUP BY p.Distributor;

The result for the above query is:

Distributor    Sales    MarketSharesVolume
  WARNER      2836192   58.131470300744400
  TF1         268668    5.506702600797200

Basically I want the MarketSharesVolume to have a values like 58.1 I tried changing the precision decimal(14, 4) but every other combination giver me an Arithmetic Overflow error. Is there a way I could truncate the column?

Upvotes: 4

Views: 743

Answers (3)

ughai
ughai

Reputation: 9890

If you are using SQL Server 2012 or above you can even use FORMAT like this.

DECLARE @v DECIMAL(18,9) = 234.234342345

SELECT @v,FORMAT(@v,'.00'),CAST(@v as DECIMAL(18,2))

Upvotes: 1

Igor Borisenko
Igor Borisenko

Reputation: 3866

You can convert data type of the column:

       SELECT p.Distributor, SUM(r.SalesVolume) as Sales, 
    CAST( (Cast(SUM(r.SalesVolume) as decimal(14, 4)) / (SELECT SUM(r.SalesVolume) FROM RawData r)) * 100 
   as decimal(20,1)) as MarketSharesVolume
    FROM RawData r
    INNER JOIN Product p
    ON r.ProductId = p.ProductId
    WHERE p.Distributor in ('TF1','WARNER')
    GROUP BY p.Distributor;

Upvotes: 1

Kaf
Kaf

Reputation: 33849

Here is an example. Using ROUND() to get the correct value and CONVERT() to get rid of trailing zeros. You can apply this to your query. 

DECLARE @Val DECIMAL(28, 18) = 58.131470300744400
SELECT CONVERT(decimal(28, 1), ROUND(@Val, 1))

Upvotes: 1

Related Questions