Difficult Query

Databases
1

Hi, I have this query:

SELECT Count(tblpeople.PersonID) AS Total, 
	YEAR(CURRENT_TIMESTAMP) - YEAR(tblmembership.MemberSince) AS YearsMember 
	
FROM tblmembership INNER JOIN tblpeople ON tblmembership.PersonID = tblpeople.PersonID
WHERE 
(
		(
			(
				tblpeople.MemSTATUS = 'Back from VPM'
			)
			OR (
				tblpeople.MemSTATUS = 'Sent To VPM 2'
			)
			OR (
				tblpeople.MemSTATUS = 'Sent To VPM 1'
			)
			OR (
				tblpeople.MemSTATUS = 'Back from VPM'
			)
			OR (tblpeople.MemSTATUS = 'Renewal')
			OR (
				tblpeople.MemSTATUS = 'Active Member'
			)
		)
	)
GROUP BY YearsMember 
HAVING YearsMember

ORDER BY YearsMember

It gets counts on years people have been a member.

What has me stumped is the boss wants it broken down by:
0 - 2
3 -5
6 - 10
11 - 20

20
So counts for each. I can not figure out how to do this, though I’m sure there’s a way!

I can just bring this into excel and manually group the counts, but wanted it done all in one shot.

Thanks

2

First of all, you can simply what you have a bit:

SELECT 
    Count(tblpeople.PersonID) AS Total, 
    YEAR(CURRENT_TIMESTAMP) - YEAR(tblmembership.MemberSince) AS YearsMember 
FROM
    tblmembership
    INNER JOIN tblpeople ON (tblmembership.PersonID = tblpeople.PersonID)
WHERE 
    tblpeople.MemSTATUS IN ('Back from VPM',  'Sent To VPM 2', 'Sent To VPM 1', 'Back from VPM', 'Renewal', 'Active Member')
GROUP BY YearsMember 
HAVING YearsMember
ORDER BY YearsMember

Many times that makes things easier to process and come up with a solution.

3

Can’t edit my post… Was suppose to be “simplify”. Second, what database are you using?

4

MySql

How would you group them by the year ranges, thats what’s got me stumped

5

Use a CASE statement, something like:

GROUP BY 
    SELECT CASE
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(tblmembership.MemberSince) <= 2 THEN '0-2'
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(tblmembership.MemberSince) <= 5 THEN '3-5'
      ... etc ...
    END

I am not too verse on MySQL so the syntax might be a bit wrong, but I’m sure you can figure it out from there.

6

Here is the working query for anybody else that needs an example:


SELECT Count(tblpeople.PersonID) AS Total, 
    CASE 
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(tblmembership.MemberSince) BETWEEN 0 AND 2 
      THEN '0-2' 
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(tblmembership.MemberSince) BETWEEN 3 AND 5 
      THEN '3-5' 
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(tblmembership.MemberSince) BETWEEN 3 AND 10 
      THEN '6-10' 
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(tblmembership.MemberSince) BETWEEN 11 AND 20 
      THEN '11-20' 
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(tblmembership.MemberSince) BETWEEN 21 AND 30 
      THEN '21-30' 
     
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(tblmembership.MemberSince) > 30 
      THEN '>30'
    END AS YearsMember 
FROM tblmembership 
    INNER JOIN tblpeople ON tblmembership.PersonID = tblpeople.PersonID
WHERE tblpeople.MemSTATUS IN ( 'Back from VPM', 'Sent To VPM 2', 'Sent To VPM 1', 
                               'Back from VPM', 'Renewal', 'Active Member')
GROUP BY YearsMember 
ORDER BY YearsMember
7

Richard, glad you got it working. I am wondering, though, if it wouldn’t be easier and less error prone to use <= 2, <= 5, etc… No need for BETWEEN. Also, the last statement could be ELSE which would also reduce potential error and remove duplicate business logic.

You can also shorten things a bit by using an AS

SELECT
    Count(p.PersonID) AS Total, 
    CASE 
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(m.MemberSince) <= 2 THEN '0-2' 
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(m.MemberSince) <= 5 THEN '3-5' 
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(m.MemberSince) <= 10 THEN '6-10' 
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(m.MemberSince) <= 20 THEN '11-20' 
      WHEN YEAR(CURRENT_TIMESTAMP) - YEAR(m.MemberSince) <= 30 THEN '21-30' 
      ELSE '>30'
    END AS YearsMember 
FROM
    tblmembership AS m
    INNER JOIN tblpeople AS p ON (m.PersonID = p.PersonID)
WHERE 
    p.MemSTATUS IN ( 'Back from VPM', 'Sent To VPM 2', 'Sent To VPM 1',  'Back from VPM', 'Renewal', 'Active Member')
GROUP BY YearsMember 
ORDER BY YearsMember

Oh, one more note. Your YEAR math could result in misleading information, depending on how your membership works. For example, Someone joins Dec 31st, 2012 and today is Jan 1st, 2015, it’ll show them in the 3-5 range, when really they are barely 2 years. You may wish to use DATEDIFF, you will have to then think in days though.

8

Thanks for posting this. It will be valuable for others in the future. Too many threads pose a question, but do not include the solution after it’s been found. (I know I’ve been remiss in not doing this myself - guess being so relieved to have a knotty problem resolved that I forget to post the working solution…)