SQL-Total problem (count)

[bordak]

Hi all,

I have a question for you.

This is the view i'm working on right now.

SELECT Group.Cod_Name,count(*)
FROM view_Group Group
INNER JOIN view_Last_Date LD ON Group.Emp_No = LD.Emp_Code AND Group.Cod_Code = LD.Cod_Code
WHERE (Group.Cod_Code = 'SEC0001')
GROUP BY Group.Cod_Name

This inner join returns me a list of employee and a boolean that tell me if they've followed the formation 'sec0001'

In the view 'view_last_date' i have this :

CASE WHEN DATEADD([MONTH], Formation.int_Frequency, Formation.dte_Date) > getdate() THEN 'True' ELSE 'False' END AS Valid

I already have the total, but i need to count for the columns Valid the total of 'True' as Total_Ok and the total of 'False' as Total_Not_Ok.

Here the result i want :

formation name,total,total_Ok,total_Not_Ok,
sec0001,97,95,2

I know it's not that complicated, but i miss a little something...

Thanks for your time.

[bordak]

I've found the answer!!

By using SUm with a Case i was able to get the total of 'true' and 'false'

select group.Cod_name, Count(*) as Total, Sum(Case LD.Valid when 'True' then 1 else 0 end) as Total_ok,
Sum(Case LD.Valid when 'False' then 1 else 0 end) as Total_not_ok

Hope it will help someone in the future!