0

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.

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 30 times (as I said before last 30 days starting from today). Take today as thirtieth day.

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

Improve this question
1
  • Do you want a running sum? If so: sum(...) OVER (...) i.e. use sum as a window function. Commented Apr 4, 2014 at 1:47

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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 ? "

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.