hello all,
i have a table that contains billing information of several types. i am trying to create a single query that sums the amount of each type in a single row per client. i am trying to use subqueries to sum the amounts per type (3 different types of billing, so 3 different subqueries).
if i run the query for a single client, the subqueries work beautifully.
if i run the query for more than one client, the first result row looks good, but all others have a grand total from 1 subquery and null from the others.

the query looks like this:

select a.id,
( select sum(z.premium)
from insured_item_value_coverage_billing z
inner join insured_item_value_coverage y
on z.insured_item_value_coverage_id = y.id
inner join policy_coverage x
on y.policy_coverage_id = x.id
inner join coverage_type w
on x.coverage_type_id = w.id
where x.policy_id = a.id
and z.billing_month = '2004-07-01'
and w.class1 = 2
) as dude
, ( select sum(z.premium)
from insured_item_value_coverage_billing z
inner join insured_item_value_coverage y
on z.insured_item_value_coverage_id = y.id
inner join policy_coverage x
on y.policy_coverage_id = x.id
inner join coverage_type w
on x.coverage_type_id = w.id
where x.policy_id = a.id
and z.billing_month = '2004-07-01'
and w.class1 = 1
) as dude2
from policy a
where a.id >= 70 and a.id < 75
--where a.id = 71

if i query for just 1 id, i get:
+----+--------+---------+
| id | dude | dude2 |
+----+--------+---------+
| 71 | 84.00 | 2214.87 |
+----+--------+---------+

if i query for the range 70 to 74, i get:
+----+---------+--------+
| id | dude | dude2 |
+----+---------+--------+
| 70 | 54.99 | 128.32 |
| 71 | 2298.87 | [NULL] |
| 72 | 5818.94 | [NULL] |
| 73 | 8544.50 | [NULL] |
| 74 | 5860.28 | [NULL] |
+----+---------+--------+

any ideas or workarounds?
thanks!

I'd rewrite the query without using the sub-queries.

-PatP

how do i rewrite the query using more than 1 sum aggregate function? could you give me a simple example, please?
note that i really need all sums to appear in the same row (i.e. cant group by coverage_type).
thanks!

I'd use:-PatP

awesome, thanks! ill give it a shot