Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011

    Unanswered: Suming rows and renaming in a query

    Ok so I have the following query

    SELECT [Bulk ID Mapping Table].[Bulk ID], Sum([policy import].COUNT) AS SumOfCOUNT, Sum([policy import].LIFE_AMOUNT) AS SumOfLIFE_AMOUNT, [policy import].CATEGORY
    FROM [Bulk ID Mapping Table] INNER JOIN [policy import] ON ([Bulk ID Mapping Table].treaty_id=[policy import].TREATY_ID) AND ([Bulk ID Mapping Table].[Retro Outlet Name]=[policy import].RETRO_OUTLET_DESCRIPTION)
    GROUP BY [Bulk ID Mapping Table].[Bulk ID], [policy import].CATEGORY;

    There are many CATEGORYs that in the end have the same result so I want too sum their totals/counts together when the query runs as an example:

    Bulk_ID Count Amount Category
    9922604 11 1078271 Terminate Retention Adjustment
    9922604 3 340837 Terminated-Other
    9922604 7 833911 Termination

    (This is just part of what the query returns)

    What I want is it to say

    Bulk_ID Count Amount Category
    9922604 21 2253019 Termination

    There are a few instances where I want something like this to happen.

    is there any easy way to do that in the query?

    Or would I have to make another query to get what I want, then somehow join them?


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    Don't join them: Use the query you have as a subquery and perform the additional computation in a query that will use the subquery in its FROM clause.
    Have a nice day!

Posting Permissions

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