Thanks craig!!

DDL for the table:

sal(id int,reportingperiod varchar(10),salary int)

Business rule:

Average salary should be calculated only when if the employees have salary in reportingperiod month january.

Table values:

id reportingperiod sal

1 ...

I can but I have a condition involved which is, when reportingperiod is jan and sal=0 then avg(sal) should be zero even if the reportingperiod apr sal has some value..

NO 🙁

I donot have any other column which defines the reportingperiod..

Its a character data type

http://www.codeproject.com/KB/database/SQL_LIKE_Operator.aspx

I hope this will help you solve the issue.

Good luck!

I have one more question regarding this..

Can we store the result set in a view.

If yes how and when will the temp table is dropped.

Please help.

Thank you,

Sravan

Thank you very much smith..

It was a great help.

ALZDBA's solution works perfectly... (atleast for me)

select Facility,

sum(case when condition_1 then 1 else 0 end),

sum(case when condition2 then 1 else 0 end)

from Table

group by Facility

Solution would look like this:

Facility_1 ...