CODEWITHSUNDEEP
phpsqlsql-server
user1601973
user1601973

Reputation: 572

Getting Count with different conditions in Select Query

The scenario is I have a column named "States" in a table, States can be 0,1,2,3 or 4. What I want to do is get a count of each state using WHERE State = in a single query.

The main purpose is I want to show the count of records (identified by their state). Like this, 20 records have State 0 etc.

Is this possible? If yes, then how can I achieve this?

Edit: I know about Count. Here's what I have tried:

SELECT State, Date_Created, (SELECT COUNT(Id) FROM [ECOS].[eco].[tb_projects_details] WHERE State=1) as State_One, (SELECT COUNT(Id) FROM [ECOS].[eco].[tb_projects_details] WHERE State=2) as State_Two, (SELECT COUNT(Id) FROM [ECOS].[eco].[tb_projects_details] WHERE State=0) as State_Zero, (SELECT COUNT(Id) FROM [ECOS].[eco].[tb_projects_details] WHERE State=4) as State_Four FROM [ECOS].[eco].[tb_projects_details] WHERE Date_Created < dateadd(week,-3,getdate());

Upvotes: 0

Views: 105

Answers (4)

Vaisakh Parannattil Cherul
Vaisakh Parannattil Cherul

Reputation: 704

It will be very easy if you use group by clause after where condition. Then you will get the number of each state very easily.

SELECT state, count(*)
FROM table_name 
GROUP BY state

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272166

To generate a list of values and their counts you can do this:

SELECT State, COUNT(*) AS C
FROM mytable
GROUP BY State

To generate one row that contains value counts as columns you can do this:

SELECT
COUNT(CASE State WHEN 0 THEN 1 ELSE NULL END) AS State_0_Count,
COUNT(CASE State WHEN 1 THEN 1 ELSE NULL END) AS State_1_Count,
COUNT(CASE State WHEN 2 THEN 1 ELSE NULL END) AS State_2_Count,
COUNT(CASE State WHEN 3 THEN 1 ELSE NULL END) AS State_3_Count,
COUNT(CASE State WHEN 4 THEN 1 ELSE NULL END) AS State_4_Count
FROM [...]
WHERE [...]

Upvotes: 1

PeterRing
PeterRing

Reputation: 1797

Next time read a sql for beginers book first.

Select state, count(*)
from table 
group by state

Upvotes: 0

Anyone
Anyone

Reputation: 2835

If I understand it correctly, you want to group and count:

SELECT   mt.States, COUNT(*) total
FROM     my_table mt
GROUP BY mt.States

Upvotes: 1

Related Questions