Complicated SQL query

Question

I have the following db tables (which is simplified to illustrate the problem)

CampaignTx

campaignTx_id | member_id | date_created | shop_id
1 | 2 | 7/12/2009 | 2
2 | 4 | 7/13/2009 | 3
3 | 6 | 7/14/2009 | 4
4 | 5 | 8/14/2009 | 3
5 | 10| 8/19/2009 | 1

Reliability

Reliability_id | campaignTx_id | status
1 | 3 | 0
2 | 2 | 1
3 | 4 | 2
4 | 5 | 3
5 | 7 | 1

Shop

Shop_id | Shop_name | City_id
1 | shop 1| 5
2 | shop 2| 7
3 | shop 3| 7
4 | shop 4| 6

City

City_id | City_name
5 | city 1
6 | city 2
7 | city 3

What I want is the following table (each row is grouped by city, year and month):

City| year | month| num_of_campaignTx_records | num_of_reliability_records | num_of reliability_records with status = 0 |num_of reliability_records with status = 1| num_of reliability_records with status = 2| num_of reliability_records with status = 3

How should I write the SQL query to get this table?

I have the following query now but I don't know how to write the last 4 columns:

select datepart(year,[Tx].date_created) as year,
datepart(month,[Tx].date_created) as month,
[city].nameTc as city,
count([Tx].date_created) as 'total num of campaign Tx records', 
count([rel].CreateDate) as 'num of reliability records'

from campaigntx as [Tx]

full join [Reliability] as [rel]
on [rel].[CampaignTx_id] = [Tx].[CampaignTx_id]

join shop as [shop]
on [Tx].shop_id = [shop].shop_id

join City as [city]
on [city].city_id = [shop].city_id

group by datepart(year,[Tx].date_created),datepart(month,[Tx].date_created), [city].nameTc

Answer 1

I haven't tested it but something on the following lines should work

sum(when [rel].status = 1 then 1 else 0) as 'num of reliability records'

Answer 2

Looks like a case for PIVOT

WITH Qry AS (
select datepart(year,[Tx].date_created) as year,
datepart(month,[Tx].date_created) as month,
[city].nameTc as city,
count([Tx].date_created) as 'total num of campaign Tx records', 
count([rel].CreateDate) as 'num of reliability records',
[re].status
from campaigntx as [Tx]

full join [Reliability] as [rel]
on [rel].[CampaignTx_id] = [Tx].[CampaignTx_id]

join shop as [shop]
on [Tx].shop_id = [shop].shop_id

join City as [city]
on [city].city_id = [shop].city_id

group by datepart(year,[Tx].date_created),datepart(month,[Tx].date_created), [city].nameTc
)
SELECT year, month, city, [toal num of campaign Tx records], 
  [0] as [No status 0], [1] as [No status 1], [2] as [No status 2], ...
 FROM Qry
PIVOT ( SUM([number or reliability records]) FOR status in ([0],[1],[2],...) ) AS PVT;