Home » SQL & PL/SQL » SQL & PL/SQL » Query Help (ORACLE 10G)
Query Help [message #415363] Mon, 27 July 2009 07:16 Go to next message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi All,

I have a problem in grouping a column based on its values.
Below is the scenario

EMPID AVG RESULT

38124 10 FAILED
38124 30 INCOMPLETE
38124 00 NOT ATTEMPTED
38124 94 PASSED


I have the above output displayed for single user.
I need a single line output for a single user.
I wanted to have an extra column called "STATUS" which depends on output of "RESULT" column.
Its similar to logic design concept.
STATUS column should posses values only PASSED OR INCOMPLETE

PASSED value should display only if a user is having all rows status as "PASSED" and if one row having FAILED/NOT ATTEMPTED/INCOMPLETE then it should display only "INCOMPLETE".

How can we go further to accomplish this.

Thanks in adv.

Sanjay
Re: Query Help [message #415373 is a reply to message #415363] Mon, 27 July 2009 08:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
One way is to assign a value to each of the individual statuses (translate them in your query using decode) and then add the values.
If you assing ZERO to the status PASSED, the sum should never exceed ZERO

Example:
create table faq (emp_id number, status varchar2(15));
insert into faq values (1, 'Passed');
insert into faq values (1, 'Passed');
insert into faq values (1, 'Passed');
insert into faq values (1, 'Passed');
insert into faq values (2, 'Passed');
insert into faq values (2, 'FAILED');
insert into faq values (2, 'Not Passed');
insert into faq values (2, 'Passed');
insert into faq values (3, 'Wrong');
insert into faq values (3, 'Invalid');

select emp_id
,      decode( sum(decode(status, 'Passed', 0, 1))
             , 0, 'Passed'
             , 'Rejected'
             )
from   faq
group  by emp_id
/

[Updated on: Mon, 27 July 2009 08:26]

Report message to a moderator

Re: Query Help [message #415374 is a reply to message #415363] Mon, 27 July 2009 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

[Updated on: Mon, 27 July 2009 08:24]

Report message to a moderator

Re: Query Help [message #415377 is a reply to message #415363] Mon, 27 July 2009 08:29 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
You could do it in the following way:
Processing ...
WITH MY_TAB AS (
	SELECT 1 AS EMPID,4 AS VALUE, 'FAILED' AS RESULT
	FROM DUAL
	UNION ALL
	SELECT 1 AS EMPID,4 AS VALUE, 'PASSED' AS RESULT
	FROM DUAL
	UNION ALL
	SELECT 1 AS EMPID,2 AS VALUE, 'NOT ATTEMPTED' AS RESULT
	FROM DUAL
	UNION ALL
	SELECT 1 AS EMPID,2 AS VALUE, 'INCOMPLETE' AS RESULT
	FROM DUAL
	UNION ALL
	SELECT 2 AS EMPID,5 AS VALUE, 'PASSED' AS RESULT
	FROM DUAL
	UNION ALL
	SELECT 2 AS EMPID,4 AS VALUE, 'PASSED' AS RESULT
	FROM DUAL
	UNION ALL
	SELECT 2 AS EMPID,3 AS VALUE, 'PASSED' AS RESULT
	FROM DUAL
)
select EMPID,
	AVG(VALUE) AS AVG,
	NVL2(MIN(NULLIF(RESULT,'PASSED')),'INCOMPLETE','PASSED') AS STATUS
FROM MY_TAB
GROUP BY EMPID
Query finished, retrieving results...
                 EMPID                                   AVG                    STATUS   
-------------------------------------- -------------------------------------- ---------- 
                                     1                                      3 INCOMPLETE 
                                     2                                      4 PASSED     

2 row(s) retrieved


Bye Alessandro
Re: Query Help [message #415478 is a reply to message #415377] Tue, 28 July 2009 01:22 Go to previous message
sanjay6713
Messages: 40
Registered: June 2009
Location: INDIA
Member
Hi All,

Thanks on the reply.
That really solved my issue.

I tried both Alessandro query and Frank query.
Both query works the same and is really a good and efficient one.

And for Michael, Sure i will improve on my formatting.Thanks on update.

Thanks,
Sanjay
Previous Topic: EXECUTE IMMEDIATE
Next Topic: No data showing up in synonyms
Goto Forum:
  


Current Time: Fri Dec 02 14:10:40 CST 2016

Total time taken to generate the page: 0.05483 seconds