Home » SQL & PL/SQL » SQL & PL/SQL » Decode Function
Decode Function [message #229599] Mon, 09 April 2007 06:26 Go to next message
arsheshadri
Messages: 12
Registered: January 2006
Junior Member
Hi,

Is there a way to display the below results in single row?

Select
DECODE(PI,'Q',Count(*)) Q_COUNT,
DECODE(PI,'H',Count(*)) H_COUNT,
DECODE(PI,'A',Count(*)) A_COUNT,
DECODE(PI,'O',Count(*)) O_COUNT,
DECODE(PI,' ',Count(*)) Space_COUNT
From STAGING_DATA
Group by PI;


I am getting result as below

 Q_COUNT    H_COUNT    A_COUNT    O_COUNT    SPACE_COUNT
---------- ---------- ---------- ---------- -----------
                                                  44677
                             562
                                        118
I wanted the results in below manner

Q_COUNT    H_COUNT    A_COUNT    O_COUNT    SPACE_COUNT
---------- ---------- ---------- ---------- -----------
                          562        118       44677


Thanks for your help.
Sheshadri
Re: Decode Function [message #229616 is a reply to message #229599] Mon, 09 April 2007 07:02 Go to previous messageGo to next message
rigatonip
Messages: 50
Registered: December 2005
Member
It looks like you should be able to just sum them up. Something like:

select
  sum(q_count) q_count,
  sum(h_count) h_count,
  sum(a_count) a_count,
  sum(o_count) o_count,
  sum(space_count) space_count
from
(
  select
  decode(pi,'Q',count(*)) q_count,
  decode(pi,'H',count(*)) h_count,
  decode(pi,'A',count(*)) a_count,
  decode(pi,'O',count(*)) o_count,
  decode(pi,' ',count(*)) space_count
  from testit
  group by pi
);



Re: Decode Function [message #229641 is a reply to message #229599] Mon, 09 April 2007 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just remove the "group by pi".

Regards
Michel
Re: Decode Function [message #229661 is a reply to message #229641] Mon, 09 April 2007 09:27 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No, that would most probably return more than one row
Re: Decode Function [message #229663 is a reply to message #229661] Mon, 09 April 2007 09:51 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without the "group by" it can't return more than one row.
But if I understand what's the OP want to do, the correct query should be:
Select
SUM(DECODE(PI,'Q',1)) Q_COUNT,
SUM(DECODE(PI,'H',1)) H_COUNT,
SUM(DECODE(PI,'A',1)) A_COUNT,
SUM(DECODE(PI,'O',1)) O_COUNT,
SUM(DECODE(PI,' ',1)) Space_COUNT
From STAGING_DATA

Regards
Michel

Previous Topic: Surrogate key
Next Topic: Getting Error Encountered the symbol "@" when expecting one when excuting procedure in ora
Goto Forum:
  


Current Time: Fri Dec 09 21:12:08 CST 2016

Total time taken to generate the page: 0.13855 seconds