> For the complete documentation index, see [llms.txt](https://docs.locoia.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.locoia.com/data-and-dashboards/dashboards-and-insights/useful-sql-examples.md).

# Useful SQL Examples

## 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:

```sql
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 <mark style="color:red;">`ROUND(your_decimal_values, n)`</mark>. You can format the output in PostgreSQL with the function <mark style="color:red;">`to_char()`</mark> and specify the number of decimal places after the point. Here an example for round to the second decimal place:

```sql
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:

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


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.locoia.com/data-and-dashboards/dashboards-and-insights/useful-sql-examples.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.