CODEWITHSUNDEEP
sqloracle-database
VIGNESH R
VIGNESH R

Reputation: 63

Error in SQL command using Group by?

I'm beginner in Oracle SQL. I am using SQL Developer. This query is not executed. I need for each id like 1001,1002 how many no of yes status and how many no of no status. Thanks in advance....

I used this SQL:

SELECT ID, COUNT(STATUS) 
FROM TABLE1 
WHERE 
GROUP BY ID, STATUS 
HAVING STATUS = YES OR STATUS = NO;

I have table like this:

id      school    status
--------------------------
1001    vani         YES
1002    sunbeam      YES
1001    shristri     YES
1002    jain         NO
1001    holycross    YES
1001    vani         NO

I need output like

id    yesstatus   Nostatus
-------------------------
1001      3        1
1002      1        1

Upvotes: 3

Views: 128

Answers (3)

Alireza Shabani
Alireza Shabani

Reputation: 64

CREATE TABLE dbo.TABLE1  
(
id INT,
school NVARCHAR(50)  ,
status BIT
)
INSERT INTO dbo.TABLE1 ( id ,
                         school ,
                         status )
VALUES  (1001,'vani',1),
        (1002,'sunbeam', 1),
        (1001,'shristri',1),
        (1002,'jain',0),
        (1001,'holycross',1),
        (1001,'vani', 0)

SELECT id,
   SUM(CASE WHEN status=1 THEN 1 ELSE 0 END) as yesstatus,
   SUM(CASE WHEN status=0 THEN 1 ELSE 0 END) as Nostatus
 FROM dbo.TABLE1 
GROUP BY id

Upvotes: 0

Pawan Choudhary
Pawan Choudhary

Reputation: 1

select  status, count(status) 
from your_table
group by status

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Your current query syntax is really wrong, but you can do conditional aggregation :

select id, 
       sum(case when status = 'YES' then 1 else 0 end) as yesstatus,
       sum(case when status = 'NO' then 1 else 0 end) as Nostatus      
from table1 t1
where status in ('YES', 'NO')
group by id;

Upvotes: 8

Related Questions