Hello,
This is an extract from a table which I am trying to run a query against: (Pardon the poor formatting... life just isn't simple today!)
ID Policy Lives
1000 123456 500
1000 123456 500
1500 453451 73
1750 453472 45
2000 123456 500
2000 123456 500 (note, repeated)
2000 456714 1000
2000 876741 123
4054 123456 500
The policy number will always equal the same number of lives, i.e. 123456 = 500 lives. What I want to do is get each distinct ID (1000, 1500, 1750, etc.) and the number of lives for each policy attached to that ID. Once that number has been calculated, sum the total. E.g.:
1000 123456 500
1500 453451 73
1750 453472 45
2000 123456 500
2000 456714 1000
2000 876741 123
4054 123456 500
======
1841
I'm hoping to be able to do this through SQL only - I would rather not have to come up with a form or report for this as it's kind of a "one-off".
I'm thinking this should be fairly simple, but it's not coming to me this morning.
Any ideas? Thanks!
This is an extract from a table which I am trying to run a query against: (Pardon the poor formatting... life just isn't simple today!)
ID Policy Lives
1000 123456 500
1000 123456 500
1500 453451 73
1750 453472 45
2000 123456 500
2000 123456 500 (note, repeated)
2000 456714 1000
2000 876741 123
4054 123456 500
The policy number will always equal the same number of lives, i.e. 123456 = 500 lives. What I want to do is get each distinct ID (1000, 1500, 1750, etc.) and the number of lives for each policy attached to that ID. Once that number has been calculated, sum the total. E.g.:
1000 123456 500
1500 453451 73
1750 453472 45
2000 123456 500
2000 456714 1000
2000 876741 123
4054 123456 500
======
1841
I'm hoping to be able to do this through SQL only - I would rather not have to come up with a form or report for this as it's kind of a "one-off".
I'm thinking this should be fairly simple, but it's not coming to me this morning.
Any ideas? Thanks!
