current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 0 down vote favorite

I have one problem in postgreSQL.

This is my table (This table does not showing all data in Image)

enter image description here

what is my requirement is

Step 1 : find count of value (this is a column in table) Order by value for today date. So It will be like this and I did it(no difficulty)

enter image description here

Step 2 : find count of value for last 30 days starting from today. I am stuck here. Also one another thing is included in this step --

Example : today has 10 count for a value - kash, this will be 10x30, yesterday had 4 count for the same value , so will be 4x29, so the total sum would be

(10x30) + (4x29) = 416.

This calculation is calculated for each and every value.

This loop execute for 3o times (as I said before last 30 days starting from today). Take today as thirstiest day.

Query will just need to return two columns with value and sum, ordered by the sum

Please help me

share|improve this question
    
Do you want a running sum? If so: sum(...) OVER (...) i.e. use sum as a window function. – Craig Ringer Apr 4 '14 at 1:47
    
Please check EDIT. – Napster Apr 4 '14 at 8:57

1 Answer 1

up vote 1 down vote

Add a WHERE clause to your existing query:

WHERE Timestamp > current_date - interval '30' day;

As far as ordering by the sum, add an ORDER BY clause.

ORDER BY COUNT(*) DESC.

I do not believe that you will need a loop (CURSOR) for this query.

share|improve this answer
    
How can I manage this - "for example, today has 10 count for a value - kash, this will be 10x30, yesterday had 4 count for the same value , so will be 4x29, so the total sum would be (10x30) + (4x29) = 416. Means how can do (10x30) + (4x29) calculation ? " – Napster Apr 4 '14 at 8:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.