Generate Report Query

Hi every body
the image below describe entity relation diagram of my database i need to Write Query the generate report of licensed buildings (that has license saved in BuildingLicense Table ) and not licensed buildings in section or street

i need to gerate report like table below

select
B.streat,
Licensed = sum(case when BL.LicNo is not null then 1 else 0 end),
NotLicensed = sum(case when BL.LicNo is null then 1 else 0 end)
from
Building B (nolock)
left outer join BuildingLicensce BL (nolock)
on BL.Building_NO = B.BuildingNo
group by
B.streat

if you also what the Serial Number column, create a temp table with identity columns and dump above results intot the temp table.

|||Thanks MR

Lakshmana Kumar K for your interest
i tried you query
in my databast i stored 5 records in buildings table and when i run query i have this result

in result the sum of licensed and not licensed buildings give the total number of buildings
in your query it gives 8 Why?

I understoond your Query
in your Query u get the count of Licensecs for buildings in the street
but i just need the number of buildings which have license Record not the Count of Licenses
|||Finally i find the Answer

-- =============================================
-- Create Report of Licensed And Notlicensed Buildings in each Street
-- Auther Mahmoud Abd El Hakeem 09/02/2007
-- =============================================
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'LicBuilPerStreet'
AND type = 'U')
DROP TABLE [LicBuilPerStreet]
GO
SELECT Street.id AS [Street],
[Count Of Licensed Buildings]=
(
SELECT count(BuildingNO)
FROM Buildings
WHERE
Buildings.Street = Street.id
AND
buildingNo IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
,
[Count Of Not Licensed Buildings] =
(
SELECT count(buildingNO)
FROM Buildings
WHERE
(
Buildings.street =Street.id
AND
buildingNo NOT IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
) INTO [LicBuilPerStreet]
FROM
Street
LEFT OUTER JOIN Buildings
ON Buildings.street = Street.id

GROUP BY Street.id

-- IF U NEED TO HIDE NULL VALUES MAKE IT RIGHT OUTER JOIN

SELECT [street].[name],[LicBuilPerStreet].[Count Of Licensed Buildings],
[LicBuilPerStreet]. [Count Of Not Licensed Buildings]
FROM [LicBuilPerStreet]
INNER JOIN Street ON
[LicBuilPerStreet].street=Street.id
--WHERE street.secid= 15
ORDER BY Street.[Name]

and this is the result

i create temp table [LicBuilPerStreet]|||Finally i find the Answer

-- =============================================
-- Create Report of Licensed And Notlicensed Buildings in each Street
-- Auther Mahmoud Abd El Hakeem 09/02/2007
-- =============================================
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'LicBuilPerStreet'
AND type = 'U')
DROP TABLE [LicBuilPerStreet]
GO
SELECT Street.id AS [Street],
[Count Of Licensed Buildings]=
(
SELECT count(BuildingNO)
FROM Buildings
WHERE
Buildings.Street = Street.id
AND
buildingNo IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
,
[Count Of Not Licensed Buildings] =
(
SELECT count(buildingNO)
FROM Buildings
WHERE
(
Buildings.street =Street.id
AND
buildingNo NOT IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
) INTO [LicBuilPerStreet]
FROM
Street
LEFT OUTER JOIN Buildings
ON Buildings.street = Street.id

GROUP BY Street.id

-- IF U NEED TO HIDE NULL VALUES MAKE IT RIGHT OUTER JOIN

SELECT [street].[name],[LicBuilPerStreet].[Count Of Licensed Buildings],
[LicBuilPerStreet]. [Count Of Not Licensed Buildings]
FROM [LicBuilPerStreet]
INNER JOIN Street ON
[LicBuilPerStreet].street=Street.id
--WHERE street.secid= 15
ORDER BY Street.[Name]

and this is the result

i create temp table [LicBuilPerStreet]
Thanks MR

Lakshmana Kumar K 4 your Interest