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

BasssqueAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
based on your previous question I think you're trying to do this

SELECT
SCHOOLS.NAME as "School",
COUNT(CASE WHEN grade_level = '9' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "9",
COUNT(CASE WHEN grade_level = '10' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "10",
COUNT(CASE WHEN grade_level = '11' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "11",
COUNT(CASE WHEN grade_level = '12' AND S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end) "12",
to_char(round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end)/ count(*) * 100, 2)) || '%' percentage
from PS.STUDENTS STUDENTS
LEFT JOIN PS.SCHOOLS SCHOOLS on STUDENTS.SCHOOLID = SCHOOLS.SCHOOL_NUMBER
LEFT JOIN PS.S_CT_STU_SPED_X S_CT_STU_SPED_X ON STUDENTS.DCID = S_CT_STU_SPED_X.STUDENTSDCID
where enroll_status=0 
AND SCHOOLID IN (3, 4, 7, 8, 9)
group by SCHOOLS.NAME

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Try this ..

CONCAT ( round(count(case when S_CT_STU_SPED_X.SPECIALEDUCATION = 'Y' THEN 1 end)/ count(*) * 100, 2) ,'%' ) percentage
0
 
BasssqueAuthor Commented:
throws the same error
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Pawan KumarDatabase ExpertCommented:
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]
0
 
BasssqueAuthor Commented:
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
0
 
Pawan KumarDatabase ExpertCommented:
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]
0
 
BasssqueAuthor Commented:
oracle 12c
that statement says missing expression
0
 
Pawan KumarDatabase ExpertCommented:
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
0
 
BasssqueAuthor Commented:
That one says missing keyword
0
 
Pawan KumarDatabase ExpertCommented:
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
0
 
BasssqueAuthor Commented:
invalid relational operator
0
 
Pawan KumarDatabase ExpertCommented:
Sir can you post your entire query..
0
 
Pawan KumarDatabase ExpertCommented:
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
0
 
BasssqueAuthor Commented:
sdstuber
I still get the following error with your example
ORA-01790: expression must have same datatype as corresponding expression
0
 
Pawan KumarDatabase ExpertCommented:
Basssque - can you try mine..? Although it is on similar lines.

I checked that it is not giving any error on live SQL..
0
 
BasssqueAuthor Commented:
nevermind, sdstubers example does work after all!  Thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.