CODEWITHSUNDEEP
sqlsql-server-2008
Pavan Tiwari
Pavan Tiwari

Reputation: 3187

How to Use where Clause in Count function

I am having Following data in table

Country State RequestStatus
India   MP        0
India  MP         1
Usa    At         0
Usa    At          0

How to get All the Completed, Pending and total request with Country and state wise grouped in MSSql query.

0: Pendint
1: Complete

Out put should be something like below

Country State Total Pending Complete
India    MP    2     1        1
USA      At    2     2        0

Upvotes: 0

Views: 59

Answers (3)

shree.pat18
shree.pat18

Reputation: 21757

Use this:

select country, [state], 
count(*) as Total, 
sum(case when requeststatus = 0 then 1 else 0 end) as Pending, 
sum(case when requeststatus = 1 then 1 else 0 end) as Complete
from tbl
group by country, [state]

We first group by the country and state to get aggregated data. Then, for calculating the total based on specific condition, we use case construct to filter out the matching records. e.g. If we want to get only the Pending Requests, we only increment the counter when requeststatus = 0.

Demo

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10274

SELECT Country,State , COUNT(* ) AS Total 
,SUM(CASE WHEN RequestStatus = 0 THEN 1 ELSE 0 END ) AS Pending 
,SUM(CASE WHEN RequestStatus = 1 THEN 1 ELSE 0 END ) AS Complete 
FROM @Test T 
GROUP BY Country,State

Upvotes: 2

Ionic
Ionic

Reputation: 3935

You can use PIVOT as an example.

CREATE TABLE #yourTable(Country nvarchar(10), State nvarchar(5), RequestStatus bit)

INSERT INTO #yourTable(Country, State, RequestStatus)
VALUES(N'India', N'MP', 0), (N'India', N'MP', 1)

SELECT *
FROM #yourTable
PIVOT (
    COUNT(State)
    FOR RequestStatus IN([0],[1])
) as PVT

DROP TABLE #yourTable

But this depends mainly on your expected result. Maybe you'll want to count MP and group id by RequestStatus instead. But I'm not sure as you haven't provided an example output for this.

Upvotes: 0

Related Questions