• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

SQL and percentages

I have a table that returns counts of values, on a daily basis.  i.e if I have column named users this column will have total user numbers on day1, day2, day3, etc....  Is there a way to get the percentage growth using SQl, I know the math operators are limited...  so if a user count on monday show  2 users, and the count on tuesday shows four users I want to calculate the percent growth,  and show this value on a table, showing daliy percentage growth.....
  • 3
3 Solutions

please try this out
SELECT A.MyDate, COUNT(*) AS CurCount, 
(CASE WHEN B.PrevCount IS NOT NULL THEN (COUNT(*)/B.PrevCount-1)*100 
ELSE NULL) AS GrowthPercentage
FROM MyTable A LEFT JOIN (SELECT COUNT(*) AS PrevCount FROM MyTable C) B ON A.MyDate-B.MyDate=1

Open in new window

For greater speed and less resource usage avoid a self join, use analytics.

SELECT userdate, usercnt, (usercnt / prevcnt) * 100 percent_growth
  FROM (SELECT userdate, usercnt, LAG(usercnt, 1) OVER(ORDER BY userdate ASC) prevcnt
          FROM yourtable)
The previous script shows percent growth from one day to the next.
If you want percent growth from the beginning of the period, use this....

SELECT userdate, usercnt, (usercnt / prevcnt) * 100 percent_growth_from_yesterday,
       usercnt / firstcnt * 100 percent_growth_from_first_day
  FROM (SELECT userdate, usercnt, LAG(usercnt, 1) OVER(ORDER BY userdate ASC) prevcnt,
               FIRST_VALUE(usercnt) OVER(ORDER BY userdate) firstcnt
          FROM yourtable)
AlinafeAuthor Commented:
so if my date from yesterday is 01-28-08, and todays date is 1-29-08 can I use trunc(sysdate) - trunc(sysdate - 1) to define a general date range to get the count value from day to day, to work this percentage out?  I am using a Timestamp to record my days, and I have a trigger on the columns to only record values from day to day.....

If you're using the first method yes, something like that would probably work.

again though, I highly recommend you use analytics over joins.  Much, much more efficient


Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now