Sum and Count in a Query

[Quish]

Hi

I have a general database question, that will effect the output from my database to a form which is being developed in .NET, it may seem very simple but I cannot make head nor tail from it.

I need to create a SELECT statement that will select all data from a table called HOUSE, but also count the number of instances of the word "BEDROOM" from a table ROOM which is linked to HOUSE through a link table HOUSE_ROOM. I also want collect the number of tenants who are associated with that house from table TENANT , and finally I want to collect the total rent collected from each tenant, this comes for a LOGBOOK table which is link to a house through the tenant.

Am I best deriving this data since the number of rooms and tenants is unlikely to change that often or calculate on the fly which is where my problem is. How can you create a select statement with several sum and/or count functions?

[ck9663]

Can you post some sample data and your desired output?

-- CK

[Quish]

Here is the create script

IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'OWNER')
DROP TABLE OWNER
CREATE TABLE OWNER
 (OWNER_ID INT IDENTITY(1,1) NOT NULL Primary KEY
 ,OWNER_NAME VARCHAR(50)
 ,OWNER_ADDRESS1 VARCHAR(50)
 ,OWNER_ADDRESS2 VARCHAR(50)
 ,OWNER_ADDRESS3 VARCHAR(50)
 ,OWNER_POSTCODE VARCHAR(50)
 ,OWNER_HOUSE_ID INT )

IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'HOUSE')
DROP TABLE HOUSE
CREATE TABLE HOUSE
 (HOUSE_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
 ,HOUSE_ADDRESS1 VARCHAR(50)
 ,HOUSE_ADDRESS2 VARCHAR(50)
 ,HOUSE_ADDRESS3 VARCHAR(50)
 ,HOUSE_POSTCODE VARCHAR(7)
 ,HOUSE_YEAR_BOUGHT NUMERIC 
 ,OWNER_HOUSE_ID INT NOT NULL 
 ,HOUSE_AMOUNT_BORROWED NUMERIC
 ,HOUSE_CURRENT_VALUE NUMERIC
 ,HOUSE_MORTGAGE_RATE NUMERIC
 ,HOUSE_MORTGAGE_TYPE VARCHAR(15)
 ,HOUSE_FURNISHED BIT
 ,HOUSE_RENT_ROOM_OR_HOUSE BIT)

IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'ROOM')
DROP TABLE ROOM
CREATE TABLE ROOM
 (ROOM_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
 ,ROOM_NAME VARCHAR(50) NOT NULL)

IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'HOUSE_ROOM')
DROP TABLE HOUSE_ROOM
CREATE TABLE HOUSE_ROOM
 (HOUSE_ROOM_ID INT IDENTITY(1,1)  NOT NULL PRIMARY KEY
 ,HOUSE_ID INT NOT NULL
 ,ROOM_ID INT )

IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'OWNER_HOUSE')
DROP TABLE OWNER_HOUSE
CREATE TABLE OWNER_HOUSE 
 (OWNER_HOUSE_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
 ,OWNER_ID INT 
 ,HOUSE_ID INT) 

IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'LOG_BOOK')
DROP TABLE LOG_BOOK
CREATE TABLE LOG_BOOK
 (LOG_BOOK_ID INT IDENTITY(1,1)  NOT NULL PRIMARY KEY
 ,LOG_BOOK_AMOUNT_COLLECTED NUMERIC
 ,LOG_BOOK_DATE_COLLECTED DATETIME
 ,LOG_BOOK_WEEKS_COVERED NUMERIC
 ,TENANT_ID INT )

The data i require is

house.Address1
house.Address2
house.Address3
house.Postcode
Number of bedrooms (Calculated)
house.Furnished
Number of Tenants (Calculated)
house.YearBough t
house.AmountBor rowed
house.MortgageR ate
house.CurrentVa lue
house.MortgageT ype
house.OwnerName s
Total Income (calculated)

Hope you can work with this,

Many Thanks

Quish

[Quish]

I have found that i can separate the SQL statements by a semicolon but I can imagine that this makes bad SQL and is not very performance friendly as it makes several result tables,any suggestions how would I improve it? This is what I have so far

SELECT     H.* 
FROM         HOUSE H               
WHERE     H.HOUSE_ID = 2;

SELECT     COUNT(R.ROOM_NAME) AS Rooms
FROM         HOUSE H, HOUSE_ROOM HR, ROOM R
WHERE     H.HOUSE_ID = HR.HOUSE_ID AND HR.ROOM_ID = R.ROOM_ID AND R.ROOM_NAME = 'BEDROOM' AND H.HOUSE_ID = 2;

SELECT DISTINCT o.owner_id,O.Owner_Name 
from OWNER O ,OWNER_HOUSE OH, HOUSE H 
where O.owner_id = oh.Owner_id
and OH.HOUSE_ID = H.HOUSE_ID
and h.house_id = 2;

SELECT COUNT(t.tenant_id) as Number_of_Tenants
FROM TENANT T, HOUSE H
WHERE T.House_ID = H.House_ID
AND H.House_ID = 2;

SELECT SUM (LB.LOG_BOOK_AMOUNT_COLLECTED) as INCOME_FROM_RENT_HOUSE
FROM LOG_BOOK LB, HOUSE H, Tenant T
WHERE lb.tenant_ID = t.tenant_id
and t.house_id = h.house_id
and H.HOUSE_ID = 2;

Quish

[Delerna]

This should get you started in the right direction

SELECT H.HouseID,COUNT(HR.ROOM_ID ) AS Rooms,isnull(COUNT(t.tenant_id),0) as Number_of_Tenants
FROM  HOUSE H   
LEFT JOIN HOUSE_ROOM HR ON H.HOUSE_ID = HR.HOUSE_ID 
LEFT JOIN TENANT T ON H.HOUSE_ID =T.House_ID 
GROUP BY H.HouseID