How to count the number of rows with specific data in mssql

Question

I have the following table:

Items:

ID     Type     StockExists  
01     Cellphone   T
02     Cellphone   F
03     Apparrel    T

I want to count the number of items with existing stocks, i.e., the number of rows with StockExists='T'. I was performing the query as;

Select count(StockExists) 
From [Items] where StockExists='T'

but it is always returning 1. What is the right way to do it?

Edit:

Also, how to perform another such Count operation and add them together in one row, for example,

Select count(StockExists) 
From [Items] where StockExists='T'` and `Select count(Type) 
From [Items] where Type='Cellphone'` ? 

Solution 1

SELECT 
    COUNT(*) As ExistCount 
FROM 
    dbo.Items
WHERE
    StockExists='T'

So your query should work.

Result:

EXISTCOUNT
    2

Demo

Update

How to perform another such Count operation and add them together in one row, for example, Select count(StockExists) From [Items] where StockExists='T' and Select count(Type) From [Items] where Type='Cellphone' ?

You can use SUM with CASE:

SELECT 
  ExistCount = SUM(CASE WHEN StockExists='T' THEN 1 ELSE 0 END) ,
  CellphoneCount = SUM(CASE WHEN Type='Cellphone' THEN 1 ELSE 0 END) 
FROM 
    dbo.Items

Result:

EXISTCOUNT    CELLPHONECOUNT
    2               2

Demo

Solution 2

Select Sum(Case when field = 'this' then 1 else 0 end) as Total from YourTable

Comments

[Victor Mukherjee]

thanks, it is working now. What can I do to get another count on the same table as the previous one with some different criteria and merge the two counts in a single row in different columns?