Better way of writing this

[morfasie]

Hi
I have the following query, but it seems inner selects take much longer to execute.
Is there a better way with joins to write this?

SELECT @group := c.`group name`as 'Group Name',
(select companyname(@group)),
(select sum(umbrella_premium+pensioner_premium+seniorplan_premium) from schedule_premiums where `group name` = @group ) as 'Premium Receivable',
(select sum(money_received) from schedule_premiums where `group name` = @group ) as 'Premium Received'
FROM schedule_premiums a, activegroups c
where a.`group name` = c.`group name`
group by a.`group name`

 

[PHV]

A starting point:
SELECT a.`group name`
,SUM(a.umbrella_premium+a.pensioner_premium+a.seniorplan_premium) AS 'Premium Receivable'
,SUM(a.money_received) AS 'Premium Received'
FROM schedule_premiums a INNER JOIN activegroups c ON a.`group name` = c.`group name`
GROUP BY a.`group name`

Where is companyname coming from ?

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

 

[morfasie]

Sorry the function companyname is a stored procedure that just returns the name of the company for me.

thanks

 

[morfasie]

So a person should always rather use an inner join or join than the inner select?

Can I use inner join on inner join for example wanting stuff from two or more tables?

Thanks

 

[PHV]

Ah, a stored procedure.
SELECT a.`group name`, companyname(a.`group name`) AS Company
,SUM(a.umbrella_premium+a.pensioner_premium+a.seniorplan_premium) AS 'Premium Receivable'
,SUM(a.money_received) AS 'Premium Received'
FROM schedule_premiums a INNER JOIN activegroups c ON a.`group name` = c.`group name`
GROUP BY a.`group name`

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

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

 

[morfasie]

Problem is when I introduce another table, all the different values get messed up. Like this?

SELECT @group := a.`group name`,(select companyname(@group))
,SUM(a.umbrella_premium+a.pensioner_premium+a.seniorplan_premium) AS 'Premium Receivable'
,SUM(a.money_received) AS 'Premium Received'
,SUM(d.amount_paid) as 'Claims Made'
FROM schedule_premiums a
Inner JOIN claim_payment_new d
ON a.`group name` = d.`group name`
and a.month = d.date_finalised
INNER JOIN activegroups c
ON a.`group name` = c.`group name`

GROUP BY a.`group name`

 

[PHV]

Sorry, I'm not a mind reader and my crystal bowl is in vacation today.
Could you please explain clearly your issue ?

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