joins or inner selects

[morfasie]

I have 2 tables with a group name, date and premium column

I want to sum both of them for a specific month.

Table 1
--------
group 1 | 2005-12-01 | 50 |
group 1 | 2005-12-01 | 50 |
group 2 | 2005-01-01 | 40 |
group 1 | 2005-01-01 | 50 |
group 1 | 2005-12-01 | 40 |

Table 2
--------
group 3 | 2005-12-01 | 10 |
group 4 | 2005-01-01 | 40 |
group 3 | 2005-01-01 | 50 |
group 3 | 2005-12-01 | 10 |

so my sql answer should be :

2005-12-01 | 140 | 20 |


I have been using this

select sum(a.totalpremium), sum(b.totalpremium)
from `productioncalc` a
inner join `policies` b
on a.`group name` = b.`group name`
and a.`inception date` = b.`inception date`

where a.`inception date` = '2005-12-01'

its not working, its mulitplying everything togerther etc, should I use normal select or is my join wrong? I am using mysql 4

 

[cLFlaVA]

would something like this work for you?

  select `inception date`
       , sum( `premium_1` ) p_1_total
       , sum( `premium_2` ) p_2_total
    from ( select `inception date`
                , `totalpremium` premium_1
                , 0 premium_2
             from `table 1`

            union all

           select `inception date`
                , 0 premium_1
                , `totalpremium` premium_2
             from `table 2`)
group by `inception date`

*cLFlaVA
----------------------------
[tt]( <P> <B>)[sup]13[/sup] * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[morfasie]

Hi thanks man but unfortunedly MySQL 4 doesn't allow nested selects.

I have to get another way?

thanks for the help

 

[hvass]

I think the closest you will get would be

select
inceptiondate,sum(totalpremium)
from productioncalc group by inceptiondate
having inceptiondate='2005-12-01'
union all
select
inceptiondate,sum(totalpremium)
from policies group by inceptiondate
having inceptiondate='2005-12-01';


Unless you create a temporary table which would be my prefered route

drop temporary table if exists temptable;
create temporary table temptable
select
1 as source,inceptiondate,totalpremium
from productioncalc
union all
select
2 as source,inceptiondate,totalpremium
from policies;

select
inceptiondate,
sum(if(source=1,totalpremium,0)),
sum(if(source=2,totalpremium,0))
from temptable
group by inceptiondate
having inceptiondate='2005-12-01';

 

[guelphdad]

You need to clarify your statement about MySQL 4. MySQL 4.0 does not do nested selects, MySQL 4.1 and above does.

You never need to use nested selects since all can be rewritten as joins.

 

[guelphdad]

Also note your JOIN in the original question is giving a cross join effect since none of the groups in table 2 are present in table 1. you would get a correct join if there were common groups present.