I am trying to get a count on approved orders in a separate column. Initial Table looks like this,

User name	Status	Count
User 1	Approved	1
	Rejected	2
User 2	Approved	5
User 3	Approved	1
User 4	Approved	2
	Rejected	5

But I want to get another column as a Approved Count,

User name	Status	Count	Approved Count
User 1	Approved	1	1
	Rejected	2
User 2	Approved	5	5
User 3	Approved	1	1
User 4	Approved	2	2
	Rejected	5

What is the best way of doing this?

CodePudding user response：

You probably want to do something like this:

Select Count(User_Id) FROM table WHERE Status = 'Approved'

You could also try adding GROUP BY User_Id at the end

CodePudding user response：

You could try something using a case statement.

SELECT Username, 
       Status, 
       Count,
       CASE 
            WHEN Status = 'Approved' Then Count
            else ''
            end as approved_count
            
 FROM table1

db fiddle

CodePudding user response：

Try this one. I used same column names as you mentioned, but not recommended

Your impotent part is this one:

case when Status = 'Approved' then count else 0 end

Example

select *, case when Status = 'Approved' then count else 0 end   Approved_count from  table_X
where row_num = 1;

Full sample code

select *, case when Status = 'Approved' then count else 0 end   Approved_count from (
select  ROW_NUMBER() OVER (partition by user_name, Status ORDER BY user_name) row_num, 
user_name, Status, count(Status) over (partition by user_name, Status) count  
from [Count_test] )
A where row_num = 1;