How to conect grand total from table 1 with the subtotals in table 2

[cristi08]

Example of GRAND TOTAL, TOTAL1 TOTAL2 TOTAL 3 SUBTOTAL 1, SUBTOTAL 2 SUBTOTAL 3
How many chairs we have in our village?
Grand total chairs in houses =total chair in house A + total chair in house B
Total chair in house A = TOTAL CHAIR IN ROOM 1 + TOTAL CHAIR IN ROOM 2
TOTAL CHAIR IN ROOM1 = SUBTOTAL FOR REED CHAIR + SUBTOTAL FOR GREEN CHAIR
TOTAL CHAIR IN ROOM 2 = SUBTOTAL FOR REED CHAIR + SUBTOTAL FOR GREEN CHAIR
---------<AND SO ON FOR HOUSE B ----->---------------------------------------------------------------------------------------I have the totals each one in his own table with his own id I wish to echo them by needs in one final report
Detailed report with chair by color, for each room or village or house
Detailed report with house by nr of chairs in it for each house
I have tables with totals for each group. now I nedd to put them toghether by diferent criteria, please say you know how to do it.

[griswolf]

I would do it this way:

create table house (
  id integer auto_increment primary key,
  name varchar(25)
);
create table chair (
  id integer auto_increment primary key,
  description varchar(16)
);
create table house_chair (
  house_id integer, /* which house has a chair like this */
  chair_id integer, /* which chair type is it */
  room varchar(16), /* which room (in this house) holds some chair(s) (of this type) */
  count integer, /* how many chairs (of this type) in this room (in this house) */

  index (house_id,chair_id, room)
);

Then for example, to get the total number of red chairs:

SELECT SUM(hc.count) as redChairCount 
  from house_chair as hc 
  join chair on hc.chair_id = chair.id 
WHERE chair.description = 'red';

To get the number of chairs in house 'griswolf':

SELECT SUM(hc.count) as griswolfChairCount
  from house_chair as hc
  join house on hc.house_id = house.id
  join chair on hc.chair_id = chair.id
WHERE house.name = 'griswolf';

This "join table" technique is (approximately) the right way to do the job. Having distinct tables for distinct houses is (approximately) the wrong way.

The point of this lesson (well, one of the points) is to help you lose the (C, Java, C# etc) programmer's mindset and help you see how SQL is a descriptive language, not a prescriptive one; and how you need to think about relations when you design your schema.

[cristi08]

Thank you very much for your reply I think it in deed what I need!