asked on

adding % symbol to a percentage - oracle query

When attempting to use the following statement I get ORA-01790: expression must have same datatype as corresponding expression. Can someone tell me how I can calculate this percentage and add the % symbol at the end? Thanks!!

cast(round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end)/ count(*) * 100, 2) as varchar(10)) || '%' as percentage

Open in new window

Pawan Kumar

Try this ..

CONCAT ( round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end)/ count(*) * 100, 2) ,'%' ) percentage

Basssque

ASKER

throws the same error

Pawan Kumar

or may be below...

SELECT ( CAST( round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) / count(*) * 100, 2) AS VARCHAR(100)) || '%' ) as [Percentage]

Basssque

ASKER

Still same error
so far it only works without trying to add the percent sign
round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end)/ count(*) * 100, 2) percentage

Pawan Kumar

Try..Which Oracle version are you using..

SELECT CAST (

ROUND( ( COUNT(CASE WHEN S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 END) / COUNT(*) ) * 100 , 2 )

, AS VARCHAR(100)) || CAST ( '%' AS VARCHAR(2)) as [Percentage]

Basssque

ASKER

oracle 12c
that statement says missing expression

Pawan Kumar

SELECT CAST (

ROUND( ( COUNT(CASE WHEN S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 END) / COUNT(*) ) * 100 , 2 )

, AS VARCHAR(100)) || TO_CHAR ( '%' (2)) as Percentage

Basssque

ASKER

That one says missing keyword

Pawan Kumar

I think that is dual..

SELECT CAST (

ROUND( ( COUNT(CASE WHEN S_CT_STU_SPED_X.SPECIALEDUCATION FROM dual = 'Y' THEN 1 END) / COUNT(*) ) * 100 , 2 )

, AS VARCHAR(100)) || TO_CHAR ( '%' (2)) as Percentage

Basssque

ASKER

invalid relational operator

Pawan Kumar

Sir can you post your entire query..

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Thanks sdstuber... Didnt thought that way.. I thought issue was at %..

SELECT
TO_CHAR(
ROUND( ( COUNT(CASE WHEN S_CT_STU_SPED_X.SPECIALEDUCATION FROM dual = 'Y' THEN 1 END) / COUNT(*) ) * 100 , 2 ))
|| '%' as Percentage

Basssque

ASKER

sdstuber
I still get the following error with your example
ORA-01790: expression must have same datatype as corresponding expression

Pawan Kumar

Basssque - can you try mine..? Although it is on similar lines.

I checked that it is not giving any error on live SQL..

Basssque

ASKER

nevermind, sdstubers example does work after all! Thanks!!