Results 1 to 2 of 2

Thread: Need Help with MySQL problem

  1. #1
    Junior Member Nicon's Avatar
    Join Date
    Jul 2006
    Location
    South Africa
    Posts
    23

    Need Help with MySQL problem

    I don't know if this is the wright place to ask for this kind of help but here goes,
    I working on a problem with MySQL it is to devide 201 separate groups and then connect then to 3 diverent subfields and then again into 4 diverent subfield, the query looks like this .

    select @group := `group name` as 'Group Name',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%FAMILY' and `cover amount` = '3000') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%FAMILY' and `cover amount` = '3000') as 'fam 3000',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%FAMILY' and `cover amount` = '5000') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%FAMILY' and `cover amount` = '5000') as 'fam 5000',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%FAMILY' and `cover amount` = '7500') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%FAMILY' and `cover amount` = '7500') as 'fam 7500',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%FAMILY' and `cover amount` = '10000') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%FAMILY' and `cover amount` = '10000') as 'fam 10000',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%CHILD' and `cover amount` = '3000') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%CHILD' and `cover amount` = '3000') as 'single+child 3000',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%CHILD' and `cover amount` = '5000') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%CHILD' and `cover amount` = '5000') as 'single+child 5000',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%CHILD' and `cover amount` = '7500') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%CHILD' and `cover amount` = '7500') as 'single+child 7500',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%CHILD' and `cover amount` = '10000') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%CHILD' and `cover amount` = '10000') as 'single+child 10000',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%SINGLE' and `cover amount` = '3000') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%SINGLE' and `cover amount` = '3000') as 'single 3000',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%SINGLE' and `cover amount` = '5000') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%SINGLE' and `cover amount` = '5000') as 'single 5000',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%SINGLE' and `cover amount` = '7500') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%SINGLE' and `cover amount` = '7500') as 'single 7500',
    (select count(*) from productioncalc where `group name` = @group and cancellationdate is null and plan_id like '%SINGLE' and `cover amount` = '10000') +
    (select count(*) from policies where `group name` = @group and cancellationdate is null and planid like '%SINGLE' and `cover amount` = '10000') as 'single 10000',
    if ((select min(`inception date`) from productioncalc where `group name` = @group and cancellationdate is null) is null,

    (select min(`inception date`) from policies where `group name` = @group and cancellationdate is null),

    (select min(`inception date`) from productioncalc where `group name` = @group and cancellationdate is null)) as 'Inception Date'



    from activegroups


    Now I have to add to all of that different ages if any one has any ideas on how to add the ages or how to simplify the query I would really appreciate it .
    Last edited by rik; July 13th, 2006 at 12:37 PM.

  2. #2
    Junior Member wjkwlf6131's Avatar
    Join Date
    Jul 2006
    Posts
    4
    You should look into using a "group by" clause. This will simplify your query extensively. One credo I have lived by when writing SQL; If it looks too complex it is probably time to rewrite it.

Posting Permissions

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