Desplay Results as a percentage

skull52
skull52 used Ask the Experts™
on
I have Script that outputs a percentage value in the results, Purchase_Percent_2014 and YTD_Purchase_Percent_2015 currently they go out 6 decimal places I would like it to display just 1 decimal place with the % symbol for example 22.5% currently it is displaying 22.500000


SELECT        VPP.Vendor_ID, VPP.Vendor_Name, VPP.Purchase_Ammount_2013, VPP.Purchase_Ammount_2014, VPP.YTD_Purchase_Ammount_2015, ISNULL(VPP.Purchase_Percent_2014, 0) AS Purchase_Percent_2014, 
ISNULL(VPP.YTD_Purchase_Percent_2015, 0) AS YTD_Purchase_Percent_2015
FROM            (SELECT        Vendor_ID, Vendor_Name, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013, SUM(CASE WHEN ([Year]) 
= 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015, 
(SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) 
= 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS Purchase_Percent_2014, (SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) 
= 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS YTD_Purchase_Percent_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Najam Uddin

Commented:
Crazy query :)

Round

& 

CONTACT
lcohanDatabase Analyst

Commented:
SELECT        VPP.Vendor_ID, VPP.Vendor_Name, VPP.Purchase_Ammount_2013, VPP.Purchase_Ammount_2014, VPP.YTD_Purchase_Ammount_2015,
      --ISNULL(VPP.Purchase_Percent_2014, 0) AS Purchase_Percent_2014,
      CONVERT(VARCHAR(50),ISNULL(VPP.Purchase_Percent_2014, 0)+' %' AS Purchase_Percent_2014,
      --ISNULL(VPP.YTD_Purchase_Percent_2015, 0) AS YTD_Purchase_Percent_2015
      CONVERT(VARCHAR(50),ISNULL(VPP.YTD_Purchase_Percent_2015, 0)+' %' AS YTD_Purchase_Percent_2015
FROM            (SELECT        Vendor_ID, Vendor_Name, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013, SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015,
(SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year])
= 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS Purchase_Percent_2014, (SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS YTD_Purchase_Percent_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID
skull52IT director

Author

Commented:
Najam,
Yeah... I know it is a bit out there, but it works. I should have said I am on MSSQL 2008R2 so I can't use CONCAT
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Najam Uddin

Commented:
SELECT VPP.Vendor_ID, VPP.Vendor_Name, ROUND(VPP.Purchase_Ammount_2013,2), ROUND(VPP.Purchase_Ammount_2014,2), ROUND(VPP.YTD_Purchase_Ammount_2015,2),
      CONVERT(VARCHAR(50),ROUND(ISNULL(VPP.Purchase_Percent_2014, 0),2)+' %' AS Purchase_Percent_2014,
      CONVERT(VARCHAR(50),ROUND(ISNULL(VPP.YTD_Purchase_Percent_2015, 0),2)+' %' AS YTD_Purchase_Percent_2015
FROM          
 (SELECT  Vendor_ID, Vendor_Name
, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013
, SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014
, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015
, (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS Purchase_Percent_2014
, (SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS YTD_Purchase_Percent_2015
FROM            
SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID
skull52IT director

Author

Commented:
lcohan,
I get the following error
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
lcohanDatabase Analyst

Commented:
sorry missed the ROUND - added with 2 decimals instead of one but you can easily change it to 1 only:


SELECT        VPP.Vendor_ID, VPP.Vendor_Name, VPP.Purchase_Ammount_2013, VPP.Purchase_Ammount_2014, VPP.YTD_Purchase_Ammount_2015,
      --ISNULL(VPP.Purchase_Percent_2014, 0) AS Purchase_Percent_2014,
      CONVERT(VARCHAR(50),ISNULL(cast(round(VPP.YTD_Purchase_Percent_2014,2) as numeric(36,2)), 0))+' %' AS Purchase_Percent_2014,
      --ISNULL(VPP.YTD_Purchase_Percent_2015, 0) AS YTD_Purchase_Percent_2015
      CONVERT(VARCHAR(50),ISNULL(cast(round(VPP.YTD_Purchase_Percent_2015,2) as numeric(36,2)), 0))+' %' AS YTD_Purchase_Percent_2015
FROM            (SELECT        Vendor_ID, Vendor_Name, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013, SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015,
(SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year])
= 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS Purchase_Percent_2014, (SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS YTD_Purchase_Percent_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID
skull52IT director

Author

Commented:
Najam,

Same Error

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Database Analyst
Commented:
one more try...sorry harder to write it without being able to actually run it...


SELECT        VPP.Vendor_ID, VPP.Vendor_Name, VPP.Purchase_Ammount_2013, VPP.Purchase_Ammount_2014, VPP.YTD_Purchase_Ammount_2015,
      --ISNULL(VPP.Purchase_Percent_2014, 0) AS Purchase_Percent_2014,
      cast(ISNULL(cast(round(VPP.YTD_Purchase_Percent_2014,2) as numeric(36,2)), 0) as VARCHAR(50)) +' %' AS Purchase_Percent_2014,
      --ISNULL(VPP.YTD_Purchase_Percent_2015, 0) AS YTD_Purchase_Percent_2015
      cast(ISNULL(cast(round(VPP.YTD_Purchase_Percent_2015,2) as numeric(36,2)), 0) as VARCHAR(50)) +' %' AS Purchase_Percent_2015
FROM            (SELECT        Vendor_ID, Vendor_Name, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013, SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015,
(SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year])
= 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS Purchase_Percent_2014, (SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS YTD_Purchase_Percent_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID
skull52IT director

Author

Commented:
lcohan,
Perfect, Thanks
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
SELECT        VPP.Vendor_ID, VPP.Vendor_Name, VPP.Purchase_Ammount_2013, VPP.Purchase_Ammount_2014,
              VPP.YTD_Purchase_Ammount_2015,
              ISNULL(CAST(VPP.Purchase_Percent_2014 AS varchar(6)) + '%', '0') AS Purchase_Percent_2014,
              ISNULL(CAST(VPP.YTD_Purchase_Percent_2015 AS varchar(6)) + '%', '0') AS YTD_Purchase_Percent_2015
FROM            (SELECT        Vendor_ID, Vendor_Name, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013, SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015,
CAST((SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year])
= 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS decimal(5, 2)) AS Purchase_Percent_2014, CAST((SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS decimal(5, 2)) AS YTD_Purchase_Percent_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID
lcohanDatabase Analyst

Commented:
phew...and as said if you really want only one decimal in the percentage(I left 2 as I believe this is "regular" way to display percentages) you can change the

round(VPP.YTD_Purchase_Percent_2014,2) as numeric(36,2)

like

round(VPP.YTD_Purchase_Percent_2014,2) as numeric(36,1)

to get only one decimal place.
skull52IT director

Author

Commented:
Yeah... I know this was a twisted script, but I was given little time to do it.  That's why I had a hard time figuring out how to do the conversions, I knew i could use round in there but wasn't sure of the syntax and the concatenation of the % symbol, thanks again.
skull52IT director

Author

Commented:
Scott,
Thanks for your input I tried you code and it generated the following Error  
Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Sorry, would need to increase the ranges, didn't expect you to have that type of % gains :-):

SELECT        VPP.Vendor_ID, VPP.Vendor_Name, VPP.Purchase_Ammount_2013, VPP.Purchase_Ammount_2014,
              VPP.YTD_Purchase_Ammount_2015,
              ISNULL(CAST(VPP.Purchase_Percent_2014 AS varchar(20)) + '%', '0') AS Purchase_Percent_2014,
              ISNULL(CAST(VPP.YTD_Purchase_Percent_2015 AS varchar(20)) + '%', '0') AS YTD_Purchase_Percent_2015
FROM            (SELECT        Vendor_ID, Vendor_Name, SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2013, SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END) AS Purchase_Ammount_2014, SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) AS YTD_Purchase_Ammount_2015,
CAST((SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year]) = 2013 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year])
= 2013 THEN Purchase_Ammount ELSE 0 END), 0) AS decimal(17, 2)) AS Purchase_Percent_2014, CAST((SUM(CASE WHEN ([Year]) = 2015 THEN Purchase_Ammount ELSE 0 END) - SUM(CASE WHEN ([Year])
= 2014 THEN Purchase_Ammount ELSE 0 END)) * 100 / NULLIF (SUM(CASE WHEN ([Year]) = 2014 THEN Purchase_Ammount ELSE 0 END), 0) AS decimal(17, 2)) AS YTD_Purchase_Percent_2015
FROM            SSG_Vendor_Yearly_Totals
GROUP BY Vendor_ID, Vendor_Name, Class_ID
HAVING         (Class_ID IN ('MAN', 'VEN'))) AS VPP LEFT OUTER JOIN
(SELECT        CUSTNMBR, CUSTCLAS
FROM            RM00101
WHERE        (CUSTCLAS IN ('B', 'CF', 'COLLECTIONS', 'EMP', 'FA', 'OBS', 'PA', 'PD', 'SA', 'SD'))) AS RM ON VPP.Vendor_ID = RM.CUSTNMBR
WHERE        (RM.CUSTNMBR IS NULL)
ORDER BY VPP.Vendor_ID
skull52IT director

Author

Commented:
No problem Scott, I should have caught that myself, yeah we do have some percentage swings.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial