group by problem(plz help me)

Arihant rathi

hi i m ajay rathi..i m facing a problem in group by clause in sql query..plz any one of u help me The problem is... following are 3 queries and i need to combine all three queries into one query..because this time these queries are giving me data in three table and i am showing this data in repeater in ASP.NET with SQL SERVER 2003.. select count(*)as active from tbl_Post_Job where Company_Id_Fk = 60 and Status = 'Active' and Post_Date between '01/01/2007' and '01/01/2008' group by year(Post_Date), month(Post_Date) select count(*)as billed from tbl_Post_Job where Company_Id_Fk = 60 and Status = 'Inactive' and Post_Date between '01/01/2007' and '01/01/2008' group by year(Post_Date), month(Post_Date) select count(*)as billed , month(Post_Date) as month_date ,year(Post_Date)as year_date from tbl_Post_Job where Company_Id_Fk = 60 and Status='Inactive' group by year(Post_Date), month(Post_Date) actually the main problem i m facing is this that when i need month and year together and active postion and billed position saperatlly.. actually i need this data in following form... Date | Active | Billed ------------------------------------ 9,2007 | 4 | 8 10,2007 | 7 | 2 12,2007 | 6 | 6 ------------------------------------ please if any one of you can help me it's very good for me plzzzzzzzzzzzzzz thanks

Ajay Rathi software engineer NOIDA(UP),INDIA

andyharman

Hi Ajay Try something like:

select month(Post_Date) as Post_Month,
    year(Post_Date) as Post_Year,
    sum(case when Status = 'Active' then 1 else 0 end) as Active,
    sum(case when Status = 'Inactive' then 1 else 0 end) as Billed
from tbl_Post_Job
where Company_Id_Fk = 60
group by year(Post_Date), month(Post_Date)
order by year(Post_Date), month(Post_Date)

The case expressions allow you to count the number of rows that match their criteria. Regards Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Arihant rathi

thx a lot of thx it's working u gave me a big support.... but i have one more problem related to this problem...actually when i m using that query which u gave me that is not working when i m using a sub query in that query ... following is that query.. sum(case when Type_of_view like 'Applicants' and Posting_Id_Fk in (select Id_PK from tbl_Post_Job where Company_Id_Fk = @Company_Id_Fk and Job_Title like @Job_Title and Job_Type like @Job_Type and Job_Location like @Job_Location ) then 1 else 0 end) as Applicants, in this query i know the value of @Company_Id_Fk, @Job_Type, @Job_Location ,@Job_Title

Ajay Rathi software engineer NOIDA(UP),INDIA