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

Leave a Reply