Solved

adding % symbol to a percentage - oracle query

Posted on 2016-10-04
16
66 Views
Last Modified: 2016-10-04
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

0
Comment
Question by:Basssque
  • 8
  • 7
16 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41828210
Try this ..

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

Author Comment

by:Basssque
ID: 41828212
throws the same error
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41828221
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:Basssque
ID: 41828227
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41828232
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
 

Author Comment

by:Basssque
ID: 41828237
oracle 12c
that statement says missing expression
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41828242
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
 

Author Comment

by:Basssque
ID: 41828245
That one says missing keyword
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41828251
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
 

Author Comment

by:Basssque
ID: 41828257
invalid relational operator
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41828259
Sir can you post your entire query..
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 41828308
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41828311
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
 

Author Comment

by:Basssque
ID: 41828341
sdstuber
I still get the following error with your example
ORA-01790: expression must have same datatype as corresponding expression
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41828343
Basssque - can you try mine..? Although it is on similar lines.

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

Author Comment

by:Basssque
ID: 41828346
nevermind, sdstubers example does work after all!  Thanks!!
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question