Useful SQL Examples

Some SQL Examples that might help you calculate that one value.

Calculating a cumulative Value

Situation: You have a calculation that tells you e.g. daily new subscribers. Now you want to add a bar-chart that displays the running total for each day (but this value doesn't exist anywhere). We can achieve this by joining the table onto itself:

SELECT 
    t1.date,
    (
    SELECT
        SUM(new_subscriptions) 
        FROM 
            daily_subscriber_change t2 where t1.date >= t2.date
        ) active_subscribers
FROM daily_subscriber_change t1
WHERE 
    date >= "$start_date"
AND 
    date <= "$end_date"

Rounding Values for displaying

If you want to display a decimal value with a certain number of decimal places, please do not use the function ROUND(your_decimal_values, n). You can format the output in PostgreSQL with the function to_char() and specify the number of decimal places after the point. Here an example for round to the second decimal place:

SELECT to_char(your_decimal_values, 'FM999999999.00') FROM yourtable;

Another option is to round the value as an integer after multiplying it by the desired number of decimal places. Here is an example of rounding to the fourth decimal place:

SELECT ROUND(your_decimal_values*10000)/10000 FROM yourtable;