# SQL: SELECT SUM of column2 in relation to column1?

**openspace**Mar 21, 2015 4:24 PM

### Title

SQL: SELECT SUM of column2 in relation to column1?

### Post

I would like to calculate the the Total $$ amount of lapsed memberships in my database that did not also renew given a StartDate and EndDate. The SQL calculation below attempts this but returns an incorrect result. Below is some sample data and the desired result. I'm running fm 13.

Some sample data:

StartDate=2014/04/01 EndDate=2015/03/11

ContactIDs | Creation | Expiry | Renewal | Name | Total |

111 | 2013-12-01 | 2014-12-01 | 2013-12-01 | Anne | 30 |

111 | 2013-12-01 | 2015-12-01 | 2014-12-01 | Anne | 20 |

112 | 2013-12-01 | 2014-12-01 | 2013-12-01 | Bob | 50 |

dollarsexpired =

ExecuteSQL ("

SELECT SUM (Total)

FROM \"Line Items\"

WHERE MDateExpiry >= StartDate

" ;¶; ¶) ;

dollarsrenewed =

ExecuteSQL

SELECT SUM (Total)

FROM \"Line Items\"

WHERE MDateRenewal >= StartDate

" ;¶; ¶)

] ;

dollarsexpired - dollarsrenewed )

Desired Result:

50

Longer explanation: In the example function I would like to know the $$ Total of members that lapsed, but did not renew. To find this number I subtract all of the lapsed memberships from those that renewed--this works if all the values are distinct, but shows an incorrect result when they are not (For example, when I change SELECT SUM (Total) TO SELECT DISTINCT (ContactIDs) I get a correct result of how many memberships lapsed, but when I change this back to SELECT SUM (Total) the calculation does not account for duplicate values (or members) given the start and end date. Both Anne and Bob lapsed, but Anne also renewed. Which means my result would show that Bob lapsed for a total of $50.