SQL: How to use condition in SQL statements

Question

Dear All,

If anyone can help me on this query, would be really helpful. I have a table as shown below. It has two fields Department and Status. I just wanted get the output as count of each status against each department. The required output is shown below

Table:

Department	Status
AC	Open
AC	Pending
AC	Open
Electronics	Close
Electronics	Close
Electronics	Open
TV	Open
TV	Open
TV	Close
TV	Close

Required Output:

Department	OpenStatus	Pending Status	Closed Status
AC	2	1	0
Electronics	1		2
TV	2		2

Warm Regards

A.Salam

Answer 1

SELECT Department , COUNT(CASE WHEN Status='Open' THEN status END)'Open Status',

                                   COUNT(CASE WHEN Status='Pending' THEN status END)'Pending Status'

                                 ............................................................

FROM tbl GROUP BY Department 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Great....Working fine. Thanks alot

Answer 3

Thank you for your reply...its working fine.

Now one more query, can I get 5th column as Total as a sum of (Open Status + Pending Status + closed Status)

Warm Regards

a.Salam

Answer 4

Thank you for your reply...its working fine.

Now one more query, can I get 5th column as Total as a sum of (Open Status + Pending Status + closed Status)

Warm Regards

a.Salam

Answer 5

WITH cte

AS

(

SELECT Department , COUNT(CASE WHEN Status='Open' THEN status END)'Open Status',

                                   COUNT(CASE WHEN Status='Pending' THEN status END)'Pending Status'

                                 ............................................................

FROM tbl GROUP BY Department 

)  SELECT *, SUM(open_status+...) OVER () as Total FROM cte

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 6

Thank you for your reply...its working fine.

Now one more query, can I get 5th column as Total as a sum of (Open Status + Pending Status + closed Status)

Warm Regards

a.Salam

In addition to Uri's solution you can add one more column to the existing query.

DECLARE @TBL TABLE
(
Dept varchar(20), Status varchar(20)
)
insert into @TBL
select 'AC', 'Open' union all 
select 'AC', 'Pending' union all 
select 'AC', 'Open' union all 
select 'Electronics', 'Close' union all 
select 'Electronics', 'Close' union all 
select 'Electronics', 'Open' union all 
select 'TV', 'Open' union all 
select 'TV', 'Open'union all 
select 'TV', 'Close' union all 
select 'TV', 'Close'  
SELECT Dept , 
COUNT(CASE WHEN Status='Open' THEN status END)'Open Status',
COUNT(CASE WHEN Status='Pending' THEN status END)'Pending Status',
COUNT(CASE WHEN Status='Close' THEN status END)'Closed Status'
,COUNT(CASE WHEN STATUS IN('OPEN','PENDING','CLOSE') THEN STATUS END) TOTAL
FROM @TBL GROUP BY Dept 

---

Murali Krishnan

Answer 7

Hi,

  I tried the same but, result is not coming properly means, If I have row like like,

Department	OpenStatus	Pending Status	Closed Status
AC	1	2	0

Total should be 2+1+0 = 3. Now it is showing as 7

Pls advise

Thank you for your help

Warm Regards

A.Salam

Answer 8

Hi,

  I tried the same but, result is not coming properly means, If I have row like like,

Department	OpenStatus	Pending Status	Closed Status
AC	1	2	0

Total should be 2+1+0 = 3. Now it is showing as 7

Pls advise

Thank you for your help

Warm Regards

A.Salam

Can you please post the query that you are using?

---

Murali Krishnan

Answer 9

I forget partition by department 

WITH cte

AS

(

SELECT Department , COUNT(CASE WHEN Status='Open' THEN status END)'Open Status',

                                   COUNT(CASE WHEN Status='Pending' THEN status END)'Pending Status'

                                 ............................................................

FROM tbl GROUP BY Department 

)  SELECT *, SUM(open_status+...) OVER (PARTITION BY Department) as Total FROM cte

OR (sorry cannot test it)

SELECT Department , COUNT(CASE WHEN Status='Open' THEN status END)'Open Status',

                                   COUNT(CASE WHEN Status='Pending' THEN status END)'Pending Status'

                                 ............................................................,

                             COUNT(CASE WHEN Status='Open' THEN status END)+COUNT(CASE WHEN                                                Status='Open' THEN status END)'+COUNT(CASE WHEN Status='Open' THEN status END)' as Ttoal

FROM tbl GROUP BY Department 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 10

Hi Murali,

I used

SELECT ModelNo 'Model No' , sectiondesc 'Complaint Section' , COUNT(CASE WHEN Status='Open'  THEN status END)'Open Calls', COUNT(CASE WHEN Status <> 'Open'and Status <> 'Close'  THEN status END)'Pending Calls', COUNT(CASE WHEN Status='Close'  THEN status END)'Clsed Calls', count(status) 'Total Calls'
from tbl_Register group by sectiondesc, modelno

Now it is fine..I just used count(status). it gives correct values. Pls advise if anything wrong

Warm Regards

A.Salam

Answer 11

Hi Murali,

I used

SELECT ModelNo 'Model No' , sectiondesc 'Complaint Section' , COUNT(CASE WHEN Status='Open'  THEN status END)'Open Calls', COUNT(CASE WHEN Status <> 'Open'and Status <> 'Close'  THEN status END)'Pending Calls', COUNT(CASE WHEN Status='Close'  THEN status END)'Clsed Calls', count(status) 'Total Calls'
from tbl_Register group by sectiondesc, modelno

Now it is fine..I just used count(status). it gives correct values. Pls advise if anything wrong

Warm Regards

A.Salam

I am not sure why you are not directly checking pending condition. I have made few changes to your query. It should work

SELECT ModelNo 'Model No' , 
sectiondesc 'Complaint Section' , 
COUNT(CASE WHEN Status='Open'  THEN status END)'Open Calls', 
COUNT(CASE WHEN Status = 'pending'  THEN status END)'Pending Calls', 
COUNT(CASE WHEN Status='Close'  THEN status END)'Clsed Calls', count(status) 'Total Calls',
COUNT(CASE WHEN STATUS IN('OPEN','PENDING','CLOSE') THEN STATUS END) Total
from tbl_Register group by sectiondesc, modelno

---

Murali Krishnan

Answer 12

Hi  Murali,

 We cannot directly check for Pending Status. There can be multiple status like "Call Waiting", "Call Forwarded", "Tech Not available" etc...all are considered as Pending Status. Only Open/ Close can be check directly

Warm Regards

A.Salam