CODEWITHSUNDEEP
sqldatabasems-access
G. Riley
G. Riley

Reputation: 1

Using 2 sums in 2 tables while also using group by in another table

I'm having a problem while testing this query on a database. The query uses 5 tables: table_1, table_2 etc. What I'm trying to do is get a sum of deaths from table_1, and a sum of births from table_5. I wish to group these sums by an attribute called AnimalGroup in table_4 but when I do so, sums start to double up. I also wish to check which of the sums is higher. I have joined the tables. What I have now:

SELECT t4.AnimalGroup, SUM(t1.Amount * t3.DeathRate), SUM(t5.Amount * t3.LiveRate)
FROM Table_1 t1, Table_2 t2, Table_3 t3, Table_4 t4, Table_5 t5
WHERE  t1.t2ID = t2.t2ID
AND t2.t2ID = t3.t2ID
AND t3.t4ID = t4.t4ID
AND t4.t4ID = t5.t4ID
GROUP BY t4.AnimalGroup
HAVING SUM(t1.Amount * t3.DeathRate) > SUM(t5.Amount * t3.LiveRate)

The death sum isn't doubled from the beginning but until more records are added. The live sum is doubled from the beginning. I've tried a lot of things but I just can't find a solution; I think I need some sort of subquery but I don't know where. If they stop doubling up, I should have exactly what I need. I also wish to see both sums. Here is the data I get right now:

AnimalGroup|SUMDeath|SUMLive
---------------------------------
Birds      |48.6    |50.7
Cats       |30      |28
Insects    |50      |20
---------------------------------

What I want is this:

AnimalGroup|SUMDeath|SUMLive
---------------------------------
Birds      |24.3    |25.35
Cats       |15      |14
Insects    |25      |10
---------------------------------

Create table table_1 (
t1ID  integer,
t2ID  integer,
Amount  decimal(10, 4),

Constraint pk_t1
   primary key (t1ID),

Constraint fk_t1
primary key ( t2ID)
references table_2(t2ID)
);go

Create table table_2 (
t2ID  integer,

Constraint pk_t2
   primary key (t2ID)
);go

Create table table_3 (
t2ID  integer,
t4ID  integer,
Name  varchar(30),
Deathrate  decimal(10, 4),
Liverate  decimal(10,4),

Constraint pk_t3
   primary key (t2ID, t4ID),
Constraint fk_t3_t2
 foreign key(t2ID)
 references table_2(t2ID),
Constraint fk_t3_t4
foreign key(t4ID)
references table_4(t4ID)
);go

Create table table_4 (
t4ID  integer,
AnimalGroup  varchar(30),

Constraint pk_t4
   primary key (t4ID)
);go

    Create table table_5 (
t5ID  integer,
t4ID  integer,

Constraint pk_t5
primary key(t5ID),

Constraint pk_t5_t4
   foreign key (t4ID)
references table_4(t4ID)
);go

Edit: Some sample data and create script added. Query changed.

Upvotes: 0

Views: 59

Answers (1)

jarlh
jarlh

Reputation: 44786

This is probably close to what you want...

Do the group by part in a derived table. Use a case expression to pick the highest of deathrate and liverate:

select AnimalGroup, dr, lr, case when dr > lr then dr else lr end
from
(
    SELECT t4.AnimalGroup as AnimalGroup,
           SUM(t1.Amount * t3.DeathRate) as dr,
           SUM(t5.Amount * t3.LiveRate) as lr
    FROM Table_1 t1, Table_2 t2, Table_3 t3, Table_4 t4, Table_5 t5
    WHERE  t1.t1ID = t2.t2ID
     AND t2.t2ID = t3.t3ID
     AND t3.t3ID = t4.t4ID
     AND t4.t4ID = t5.t5ID
    GROUP BY t4.AnimalGroup
) dt

Upvotes: 1

Related Questions