CODEWITHSUNDEEP
sql-servercount
Victor Mukherjee
Victor Mukherjee

Reputation: 11025

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

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'` ?

Upvotes: 7

Views: 14605

Answers (3)

Vibin TV
Vibin TV

Reputation: 832

When using CASE WHEN better to use NULL than 0 in ELSE case like below

 SELECT 
      ExistCount = SUM(CASE WHEN StockExists='T' THEN 1 ELSE NULL END) ,
      TotalCount = COUNT(ID) 
    FROM 
        dbo.Items

Upvotes: 0

Anthony
Anthony

Reputation: 11

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

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460058

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

Upvotes: 9

Related Questions