- 3,724,109 Users
- 2,244,689 Discussions
- 7,850,864 Comments
Forum Stats
Discussions
Categories
- 16 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 2.1K Databases
- 620 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 32 Multilingual Engine
- 497 MySQL Community Space
- 7 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 422 SQLcl
- 62 SQL Developer Data Modeler
- 185.1K SQL & PL/SQL
- 21.1K SQL Developer
- 2.5K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.7K Development Tools
- 14 DevOps
- 3K QA/Testing
- 337 Java
- 10 Java Learning Subscription
- 12 Database Connectivity
- 72 Java Community Process
- 2 Java 25
- 12 Java APIs
- 141.2K Java Development Tools
- 8 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 16 Java SE
- 13.8K Java Security
- 4 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 147 LiveLabs
- 34 Workshops
- 10 Software
- 4 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 4 Deutsche Oracle Community
- 16 Español
- 1.9K Japanese
- 3 Portuguese
group by value count and other count to show
Hi Team
i have scenario having data like below
create table temp1 (quote_no number,status varchar(10), review_status varchar(2));
insert into temp1 values(1,'Open',Null);
insert into temp1 values(2,'Open','Y');
insert into temp1 values(5,'Open','Y');
insert into temp1 values(3,'Close',Null);
insert into temp1 values(4,'Close','Y');
insert into temp1 values(9,'Close',Null);
insert into temp1 values(11,'Close',Null);
insert into temp1 values(12,'Close','Y');
select distinct status,count(*) col1 from temp1
group by status
;
o/p
status ---col1
open ----3
close ----5
but i need to result like using group by the log of status and review_status is Y count.
open ----3 -- 2
close ----5---2
Answers
-
select distinct status,count(*) col1
,sum(
case
when reviwe_status='Y' then 1
else 0
end
) number_reviewed
from temp1
group by status
;
-
Hi,
SUM (CASE ...), as Bede showed, will work. You could also use COUNT (CASE ...), like this:
SELECT status -- or LOWER (status) AS status , COUNT (*) AS col1 -- or a more descriptive name, like number_total , COUNT ( CASE WHEN review_status = 'Y' THEN 'OK' END ) AS number_reviewed FROM temp1 GROUP BY status ORDER BY status -- or whatever ;
If you really want to display 'open' and 'close' even though the table has 'Open' and 'Close', as you said, then you just need to change the first line.
In any event, you don't need "SELECT DISTINCT". The GROUP BY clause guarantees that status will be distinct in the output
