Topic: SQL*Plus and PL/SQL >> Count and Group functions
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: Count and Group functions |
| shake |
Posted: Jun 25, 2008 10:23:41 AM |
Total Post: 55
Joined: Jun, 2008
|
Hi,
Suggest me a query which calculates the count of all customers with a flag 'Y' and customers of any other flag letter(Say 'N', 'R'). I used count(*) method with group by clause and all the flags are listed individually. I don't get the idea how to group non-'Y' flags together.
Y - yes; Accepted as a customer
N - no; not accepted
R - rejected
W - waiting
I want to find the count of all 'Y's separately and others(N,R,W) separately in a single query.
Plz Reply ; Thanks |
|
|
 Gitesh |
| Posted: Jun 26, 2008 12:19:26 AM |  |
|
Total Post: 322
Joined: May, 2005
|
select flag,count(1) from table_name
where flag='Y'
group by flag
union
select flag,count(1) from table_name
where flag='R'
group by flag
union
select flag,count(1) from table_name
where flag='N'
group by flag
union
select flag,count(1) from table_name
where flag='W'
group by flag;
This type of single query you can write and will get require result.
|
|
|
|
|
| shake |
| Posted: Jun 26, 2008 12:39:05 AM | |
|
Total Post: 55
Joined: Jun, 2008
|
Thank you Gitesh for your kind reply.
I tried with union operator too. But it provides the answers splitted, non-'Y' are not bundled together. So I tried in a different way using case .. when (by renaming all non-'Y's to 'R')and got it immediately after a few minutes of this post. THANK YOU.
select sum(total) "No. Of Customers", sum(White) "White Pages", sum(Yellow) "Yellow Pages"
from (
select t.processed_status New_Status, count(*) as total, sum(t.yellow_or_fax_amt) White, sum(t.white_or_telex_amt) Yellow,
case t.processed_status
when 'Y' then 'Y'
else 'R' end Status
from t_soh_dir_file_import t
group by t.processed_status
) listed where listed.New_Status in ('Y','R')
group by listed.New_Status;
|
|
|
|
|
| Gitesh |
| Posted: Jun 26, 2008 12:55:25 AM | |
|
Total Post: 322
Joined: May, 2005
|
Good buddy...Well try.congrates.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 843 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 268 |
| neeraj sharma | 252 |
| Ramesh Jois | 245 |
| Bishal Khetan | 207 |
|
|
