By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,194 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

Sum and Count in a Query

P: 22
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?
Mar 15 '08 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
Can you post some sample data and your desired output?

-- CK
Mar 17 '08 #2

P: 22
Here is the create script

Expand|Select|Wrap|Line Numbers
  1. IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'OWNER')
  2. DROP TABLE OWNER
  3. CREATE TABLE OWNER
  4.  (OWNER_ID INT IDENTITY(1,1) NOT NULL Primary KEY
  5.  ,OWNER_NAME VARCHAR(50)
  6.  ,OWNER_ADDRESS1 VARCHAR(50)
  7.  ,OWNER_ADDRESS2 VARCHAR(50)
  8.  ,OWNER_ADDRESS3 VARCHAR(50)
  9.  ,OWNER_POSTCODE VARCHAR(50)
  10.  ,OWNER_HOUSE_ID INT )
  11.  
  12. IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'HOUSE')
  13. DROP TABLE HOUSE
  14. CREATE TABLE HOUSE
  15.  (HOUSE_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
  16.  ,HOUSE_ADDRESS1 VARCHAR(50)
  17.  ,HOUSE_ADDRESS2 VARCHAR(50)
  18.  ,HOUSE_ADDRESS3 VARCHAR(50)
  19.  ,HOUSE_POSTCODE VARCHAR(7)
  20.  ,HOUSE_YEAR_BOUGHT NUMERIC 
  21.  ,OWNER_HOUSE_ID INT NOT NULL 
  22.  ,HOUSE_AMOUNT_BORROWED NUMERIC
  23.  ,HOUSE_CURRENT_VALUE NUMERIC
  24.  ,HOUSE_MORTGAGE_RATE NUMERIC
  25.  ,HOUSE_MORTGAGE_TYPE VARCHAR(15)
  26.  ,HOUSE_FURNISHED BIT
  27.  ,HOUSE_RENT_ROOM_OR_HOUSE BIT)
  28.  
  29. IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'ROOM')
  30. DROP TABLE ROOM
  31. CREATE TABLE ROOM
  32.  (ROOM_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
  33.  ,ROOM_NAME VARCHAR(50) NOT NULL)
  34.  
  35. IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'HOUSE_ROOM')
  36. DROP TABLE HOUSE_ROOM
  37. CREATE TABLE HOUSE_ROOM
  38.  (HOUSE_ROOM_ID INT IDENTITY(1,1)  NOT NULL PRIMARY KEY
  39.  ,HOUSE_ID INT NOT NULL
  40.  ,ROOM_ID INT )
  41.  
  42. IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'OWNER_HOUSE')
  43. DROP TABLE OWNER_HOUSE
  44. CREATE TABLE OWNER_HOUSE 
  45.  (OWNER_HOUSE_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
  46.  ,OWNER_ID INT 
  47.  ,HOUSE_ID INT) 
  48.  
  49. IF EXISTS(SELECT name FROM abacus..sysobjects WHERE name = 'LOG_BOOK')
  50. DROP TABLE LOG_BOOK
  51. CREATE TABLE LOG_BOOK
  52.  (LOG_BOOK_ID INT IDENTITY(1,1)  NOT NULL PRIMARY KEY
  53.  ,LOG_BOOK_AMOUNT_COLLECTED NUMERIC
  54.  ,LOG_BOOK_DATE_COLLECTED DATETIME
  55.  ,LOG_BOOK_WEEKS_COVERED NUMERIC
  56.  ,TENANT_ID INT )
  57.  
  58.  
  59.  
The data i require is

house.Address1
house.Address2
house.Address3
house.Postcode
Number of bedrooms (Calculated)
house.Furnished
Number of Tenants (Calculated)
house.YearBought
house.AmountBorrowed
house.MortgageRate
house.CurrentValue
house.MortgageType
house.OwnerNames
Total Income (calculated)

Hope you can work with this,

Many Thanks

Quish
Mar 17 '08 #3

P: 22
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT     H.* 
  3. FROM         HOUSE H               
  4. WHERE     H.HOUSE_ID = 2;
  5.  
  6. SELECT     COUNT(R.ROOM_NAME) AS Rooms
  7. FROM         HOUSE H, HOUSE_ROOM HR, ROOM R
  8. WHERE     H.HOUSE_ID = HR.HOUSE_ID AND HR.ROOM_ID = R.ROOM_ID AND R.ROOM_NAME = 'BEDROOM' AND H.HOUSE_ID = 2;
  9.  
  10. SELECT DISTINCT o.owner_id,O.Owner_Name 
  11. from OWNER O ,OWNER_HOUSE OH, HOUSE H 
  12. where O.owner_id = oh.Owner_id
  13. and OH.HOUSE_ID = H.HOUSE_ID
  14. and h.house_id = 2;
  15.  
  16. SELECT COUNT(t.tenant_id) as Number_of_Tenants
  17. FROM TENANT T, HOUSE H
  18. WHERE T.House_ID = H.House_ID
  19. AND H.House_ID = 2;
  20.  
  21. SELECT SUM (LB.LOG_BOOK_AMOUNT_COLLECTED) as INCOME_FROM_RENT_HOUSE
  22. FROM LOG_BOOK LB, HOUSE H, Tenant T
  23. WHERE lb.tenant_ID = t.tenant_id
  24. and t.house_id = h.house_id
  25. and H.HOUSE_ID = 2;
  26.  
  27.  
Quish
Mar 18 '08 #4

Delerna
Expert 100+
P: 1,134
This should get you started in the right direction

Expand|Select|Wrap|Line Numbers
  1. SELECT H.HouseID,COUNT(HR.ROOM_ID ) AS Rooms,isnull(COUNT(t.tenant_id),0) as Number_of_Tenants
  2. FROM  HOUSE H   
  3. LEFT JOIN HOUSE_ROOM HR ON H.HOUSE_ID = HR.HOUSE_ID 
  4. LEFT JOIN TENANT T ON H.HOUSE_ID =T.House_ID 
  5. GROUP BY H.HouseID
  6.  
Mar 19 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.