Simple SQL query to sum an alias column and group by ID.
Initial query user needed help with:
select dt.member_id, dt.NumberOfMonthsBetween FROM ( select member_id, DATEDIFF(month,date_credentialed,renewal_date) as NumberOfMonthsBetween from member_credential ) dt where dt.NumberOfMonthsBetween > 6
Current result set:
Current Result: _____________________________________________ |Member_ID | NumberOfMonthsBetween| --------------------------------------------------------------- | 48 | 11 | | 1 | 12 | | 6 | 12 | | 8 | 12 | | 8 | 8 | | 41 | 11 | | 48 | 10 | ---------------------------------------------------------------
Desired result set:
____________________________________________ |Member_ID | NumberOfMonthsBetween| --------------------------------------------------------------- | 1 | 12 | | 6 | 12 | | 8 | 20 | | 41 | 11 | | 48 | 21 | ---------------------------------------------------------------
Here is the query to do just that:
select dt.member_id, sum(dt.NumberOfMonthsBetween) FROM ( select member_id, DATEDIFF(month,date_credentialed,renewal_date) as NumberOfMonthsBetween from member_credential ) dt where dt.NumberOfMonthsBetween > 6 group by dt.member_id