CODEWITHSUNDEEP
mysqlentity-frameworklinq
Nouman Bhatti
Nouman Bhatti

Reputation: 1421

How to use count twice on same column in a table

I've a table like below. What I want is to write a query which count all the rows where exit_flag is 0 and also count all the rows where exit_flag is 1 and than subtract the both counts and return a single value. How to do that using entity framework.

id created_date        device_id exit_flag
1  2017-10-24 23:00:00  13         0
2  2017-10-24 23:00:00  13         1
3  2017-10-24 23:30:00  13         0 
4  2017-10-24 23:30:00  13         1

Upvotes: 0

Views: 153

Answers (3)

Maulik
Maulik

Reputation: 104

one way is wirite two different query to count exit_flag is 0 and exit_flag 1

1). select count(*) as flg0 from table where exit_flag = 0;

2). select count(*) as flg1 from table where exit_flag = 1;

and then substrect both value

and another

1). select count(exit_flag = 0) - count(exit_flag = 1) as flg from table

Upvotes: 1

Slava Utesinov
Slava Utesinov

Reputation: 13488

var answer = (from item in context.items
              group item by 0 into sub
              select 
              sub.Where(x => x.exit_flag == 0).Count() - sub.Where(x => x.exit_flag == 1).Count()
             ).First();

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can write something like below

select sum(exit_flag = 0),sum(exit_flag = 1),
sum(exit_flag = 0) - sum(exit_flag = 1)
from demo 
group by device_id

Demo

Using Mysql you can get your conditional count using sum function, When expression is used inside sum() it will return as boolean value 0/1

I have added group by device_id to get results per device if you want it for all the remove this part from query

Upvotes: 1

Related Questions