CODEWITHSUNDEEP
sql
Barsha Saha
Barsha Saha

Reputation: 11

Growth over Years in SQL

I have a table with member_id, joined (as in time joined) and I'm trying to figure out how to find percent growth between years. I have

SELECT YEAR(joined) AS JoinedYear, COUNT(member_id) AS Members
FROM grp_member 
GROUP BY YEAR(joined) 
ORDER BY JoinedYear asc;

this shows the number of people joining per year but need help finding growth using subqueries.

Upvotes: 0

Views: 992

Answers (3)

user14581327
user14581327

Reputation:

You need to follow the GROUP BY‌‌‌​​‌​‌‌​‌‌‌‌‌‌​​​‌​‌‌​‌‌‌‌ operator. The GROUP_CONCAT version receives the last 1 month in a result set, so if you've made the expression being subquery you would SUM it and then use your join to get the first falling in the highest outer group.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270091

Just use a window function:

SELECT YEAR(joined) AS JoinedYear,
       COUNT(*) AS Members,
       (-1 + COUNT(*) * 1.0 / LAG(COUNT(*)) OVER (ORDER BY YEAR(joined))) as growth_rate
FROM grp_member 
GROUP BY YEAR(joined) 
ORDER BY JoinedYear asc;

Upvotes: 1

Chris Schaller
Chris Schaller

Reputation: 16609

A quick web search reveals this example from SQLServerCentral: Getting Year-Over-Year growth data It your base query as a CTE and self joins on it to get the previous year value:

WITH Growth 
AS (
    SELECT YEAR(joined) AS JoinedYear, COUNT(member_id) AS Members
    FROM grp_member 
    GROUP BY YEAR(joined) 
    ORDER BY JoinedYear asc  
)
SELECT curYear.JoinedYear AS [Year], curYear.[Members]
FROM Growth curYear
LEFT JOIN Growth prevYear ON curYear.JoinedYear = prevYear.JoinedYear + 1
ORDER BY curYear.JoinedYear

Try it out here: http://sqlfiddle.com/#!18/8a3f7/5

Year Members Year-Over-Year Growth
2019 3 0
2020 6 3
2021 9 3

The overall total members is not included in this output as that should include provision for members that have lapsed or cancelled but this should be a simple primer for more complex needs.

Upvotes: 0

Related Questions