We help IT Professionals succeed at work.

SQL and percentages

Alinafe
Alinafe asked
on
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.....
Comment
Watch Question

Top Expert 2008
Commented:
hi

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
GROUP BY MyDate

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
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)
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
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)

Author

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.....

Thanks
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.