Solved

adding % symbol to a percentage - oracle query

Posted on 2016-10-04
16
39 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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

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

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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

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

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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now