How to use multiple where clause in one sql query

Question

Hello guys i need to use mutiple where clause in one sql query as follows but it can't work please help me.

select (select count(total) as 'studentMarks1' from School  where total <60 ),
       (select count(total) as 'studentMarks2' from School  where total >80 ) 
from School
where Id = '8'

Which database are you using?

Bohemian
– Bohemian ♦

2017-02-27 09:21:13 +00:00
Commented Feb 27, 2017 at 9:21 — Bohemian
– Bohemian ♦, Commented Feb 27, 2017 at 9:21

Rahul · Accepted Answer · 2017-02-27 09:20:15Z

2

You rather need to use CASE statement like

select SUM(case when total < 60 then 1 else 0 end) as 'studentMarks1',
       sum(case when total > 80 then 1 else 0 end) as 'studentMarks2' 
from School
where Id = '8'

answered Feb 27, 2017 at 9:20

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3518835 Over a year ago

Thnks @Rahul it works , what i don't know is why they use SUM , so if i need use Sum as operator like select sum(total) ' there will not be a problem in query

Rahul Over a year ago

@user3518835, that's conditional sum and only when condition meets the sum operation will happen adding 1 to the sum which is same as performing a count operation. if it has to be sum then you can do like select sum(case when condition then total else 0 end)

user3518835 Over a year ago

ooh! it woks . i am sorry to as muck ,i was not familiar with case but it's great , so whe i want to select all like select * where total >50 and where total = 40 how does this works ?

jarlh Over a year ago

Case expression, not case statement.

Radu Gheorghiu · Accepted Answer · 2017-02-27 09:20:22Z

1

You cau usually do this with an appropriate CASE statement:

SELECT COUNT(CASE WHEN total < 60 then 1 else NULL END)
    , COUNT(CASE WHEN total > 80 then 1 else NULL END)
FROM School
WHERE ID = '8'

answered Feb 27, 2017 at 9:20

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

Collectives™ on Stack Overflow

How to use multiple where clause in one sql query

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related