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.....
AlinafeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.