# 📘 Basic Usage of SQL Expressions in Grafana

SQL expressions in Grafana let you perform lightweight SQL-like transformations on the results of other queries — directly within the panel. This is great for reshaping, renaming, filtering, and aggregating data without modifying the original datasource query.

---

## 🔧 How SQL Expressions Work

SQL expressions operate on the output of **another query in the same panel**, referenced by its query letter (e.g., `A`, `B`). These expressions are parsed and executed by Grafana — not sent to an external database.

---

## 📥 Input Requirements

To work correctly, SQL expressions must use data from a **backend datasource** (e.g., Prometheus, JSON API, GitHub, etc.).

### ✅ Accepted Inputs

| Input Type                                      | SQL Mapping Behavior                                                            |
|-------------------------------------------------|----------------------------------------------------------------------------------|
| **Single table-like frame (no labels)**         | Used directly — **each field becomes a column in the SQL table**.               |
| **Full Long format** (e.g., `numeric_full_long`) | Maps directly as a flat table — no conversion needed.                           |
| **Metric frame with `FrameMeta.Type`** (e.g., `TimeSeriesWide`, `TimeSeriesMulti`, `NumericWide`, `NumericMulti`) | Automatically **converted to `full_long` format**, then treated as a table.     |

### ❌ Rejected Inputs

- Queries that **lack `FrameMeta.Type` and are not a single flat table with no labels**.
- Queries from **non-backend datasources** (e.g., local variables or dashboard-only sources).
- Responses with ambiguous frame structures that cannot be interpreted as tables or convertible metrics.

---

## 📦 What’s in a `full_long` Format?

When Grafana auto-converts a metric frame into a `full_long` table, the result typically includes:

| Field               | Description                                          |
|--------------------|------------------------------------------------------|
| `time`             | Timestamp column (if present)                        |
| `__value__`        | The numeric value                                    |
| `__metric__`       | Metric name (e.g., `cpu`, `up`, etc.)                |
| `__display_name__` | Resolved display name for visualization (e.g., legend) |
| `<label columns>`  | All label keys (e.g., `job`, `instance`, `pod`, etc.)|

These become standard SQL columns for use in expressions.

---

## 💡 SQL Expression Examples (With Inputs)

### 🟢 1. Basic projection of a time series

- **Datasource A**: Prometheus  
- **Input Query**:
  ```prometheus
  sum(up{namespace=~".*(tempo|mimir).*-0."}) by (namespace)
  ```
- **SQL Expression**:
  ```sql
  SELECT time, __value__ AS up, __display_name__ AS namespace
  FROM A
  ```

---

### 🟢 2. Aggregate across time

- **Datasource A**: Prometheus  
- **Input Query**:
  ```prometheus
  sum(up{namespace=~".*(tempo|mimir).*-0."}) by (namespace)
  ```
- **SQL Expression**:
  ```sql
  SELECT time, SUM(__value__) AS up
  FROM A
  GROUP BY time
  ```

---

### 🟢 3. Sort instant values (stat panel)

- **Datasource A**: Prometheus (instant query enabled)  
- **Input Query**:
  ```prometheus
  sum(up{namespace=~".*(tempo|mimir).*-0."}) by (namespace)
  ```
- **SQL Expression**:
  ```sql
  SELECT __value__ AS up, __display_name__ AS namespace
  FROM A
  ORDER BY __value__ DESC
  ```

---

### 🟢 4. Merge GitHub issue results

- **Datasource A**: GitHub Datasource (`grafana-github-datasource`)  
- **Input Queries**:
  - `grafana` → `is:open`
  - `loki` → `is:open`
- **SQL Expression**:
  ```sql
  SELECT title, author, repo FROM grafana
  UNION ALL
  SELECT title, author, repo FROM loki
  ORDER BY repo DESC
  ```

---

### 🟢 5. Work with mock/test table

- **Datasource A**: Grafana Mock Datasource (`grafana-mock-datasource`)  
- **Input Table (`my_table`)**:
  - `username`, `height`, `role`, `isPremium`
- **SQL Expression**:
  ```sql
  SELECT * FROM my_table
  ```

---

## 🛠 Tips and Best Practices

- Use `__value__` and `__display_name__` as generic hooks into metric values and labels.
- SQL expressions are ideal for:
  - Renaming columns for clarity
  - Reducing multiple series
  - Sorting top-N values
  - Merging data sources (via `UNION`)
- Prefer writing metric queries with valid `FrameMeta.Type` (e.g., `TimeSeriesWide`) to ensure compatibility with SQL expressions.