Using Join or normal 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

 

[PHV]

Something like this ?
SELECT theDate, SUM(TotalA), SUM(TotalB)
FROM (
SELECT `inception date` theDate, totalpremium TotalA, 0 TotalB FROM `productioncalc`
UNION ALL SELECT `inception date`, 0, totalpremium FROM `policies`
) U
GROUP BY theDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[morfasie]

Thanks but the problem is I am using mysql 4, it cannot handle inner selects.

 

[PHV]

You posted in the ANSI SQL forum and thus got an ANSI SQL solution.
If you want a mySQL answer then post in a mySQL forum.

 

[morfasie]

Sorry I moved my query to MySQL Forum.


Thanks

 

[morfasie]

O ja and by the way thanks, but it still stays SQL, I just need another way to write the query.


Thanks