Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    6

    Unanswered: multiple subquery problem in select

    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!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd rewrite the query without using the sub-queries.

    -PatP

  3. #3
    Join Date
    Jan 2004
    Posts
    6
    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!

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    SELECT a.id
    ,  Sum(CASE WHEN 2 = w.class1 THEN z.premium END) AS dude 
    ,  Sum(CASE WHEN 1 = w.class1 THEN z.premium END) AS dude2 
       FROM policy a
       INNER JOIN coverage_type w
          ON (w.policy_id = a.id)
       INNER JOIN policy_coverage x
          ON (x.coverage_type_id = w.id)
       INNER JOIN insured_item_value_coverage y
          ON (y.policy_coverage_id = x.id
       INNER JOIN insured_item_value_coverage_billing z
          ON (z.insured_item_value_coverage_id = y.id)
       WHERE  '2004-07-01' = z.billing_month
          AND 70 <= a.id
          AND a.id < 75
       GROUP BY a.id
    -PatP

  5. #5
    Join Date
    Jan 2004
    Posts
    6
    awesome, thanks! ill give it a shot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •