• 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.....
0
Alinafe
Asked:
Alinafe
  • 3
3 Solutions
 
ee_rleeCommented:
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

0
 
sdstuberCommented:
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)
0
 
sdstuberCommented:
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)
0
 
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.....

Thanks
0
 
sdstuberCommented:
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

0

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