sqlite add tag
DeFacto
Hello,

Jack Douglas kindly have helped me with my previous  [question.](https://topanswers.xyz/databases?q=2613) Thank you one more time. I would like to upgrade my sql query to get the `cumulative sum` of columns `cnt` and `total`. My query looked like that:

```
SELECT CASE WHEN Col2 IS NOT NULL THEN Col1 END Col1, Col2, cnt, total
FROM(
SELECT Col1, Col2, COUNT(*) AS cnt, SUM(Col3) AS total FROM myTable GROUP BY Col1, Col2
UNION ALL
SELECT Col1, NULL, COUNT(*), SUM(Col3) FROM myTable
GROUP BY Col1 ORDER BY Col1
)
```
| Col1 | Col2 | cnt | total |
| :-----|:-----|:----|:------|
| R1 | C1 | 2 | 7 |
| R1 | C2 | 1 | 4 |
| *null* | *null* | 3 | 11 |
| R2 | C2 | 1 | 4 |
| R2 | C3 | 1 | 8 |
| R2 | C5 | 1 | 3 |
| *null* | *null* | 3 | 15 |
[fiddle](https://dbfiddle.uk/OfIKS2gy)

I have managed to update it to:

```
SELECT CASE WHEN Col2 IS NOT NULL THEN Col1 END Col1, Col2, cnt, total
FROM(
SELECT NULL AS Col1, NULL AS Col2, COUNT(*) AS cnt, SUM(Col3) AS total FROM myTable
UNION ALL
SELECT Col1, Col2, COUNT(*), SUM(Col3) FROM myTable GROUP BY Col1, Col2
UNION ALL
SELECT Col1, NULL, COUNT(*), SUM(Col3) FROM myTable
GROUP BY Col1 ORDER BY Col1
)
```
And result looks like that:
| Col1 | Col2 | cnt | total |
| :-----|:-----|:----|:------|
| *null* | *null* | 6 | 26 |
| R1 | C1 | 2 | 7 |
| R1 | C2 | 1 | 4 |
| *null* | *null* | 3 | 11 |
| R2 | C2 | 1 | 4 |
| R2 | C3 | 1 | 8 |
| R2 | C5 | 1 | 3 |
| *null* | *null* | 3 | 15 |
[fiddle](https://dbfiddle.uk/H1ORj0IJ)

How could i update my query to get this result:

| Col1 | Col2 | cnt | total |
| :-----|:-----|:----|:------|
| R1 | C1 | 2 | 7 |
| R1 | C2 | 1 | 4 |
| *null* | *null* | 3 | 11 |
| R2 | C2 | 1 | 4 |
| R2 | C3 | 1 | 8 |
| R2 | C5 | 1 | 3 |
| *null* | *null* | 3 | 15 |
| *null* | *null* | 6 | 26 |
Top Answer
Andriy M
The easiest way to achieve what you are trying to do would be to change your `ORDER BY Col1` to something like this:

```
ORDER BY Col1 NULLS LAST, Col2 NULLS LAST
```

However, the problem here is that SQLite does not support that syntax, and always sorts nulls before the other values in the ascending order and after the other values in the descending one.  You seem to want an ascending order on both columns yet you want the nulls to come last.  Therefore you will need two additional sorting criteria to achieve the desired effect, something like this:

```
ORDER BY
  CASE WHEN Col1 IS NULL THEN 1 END,
  Col1,
  CASE WHEN Col2 IS NULL THEN 1 END,
  Col2
```

But then you will face another issue: you cannot have *expressions* in `ORDER BY` when that clause applies to a `UNION`-ed set, like in your situation.  You can only reference column aliases assigned at the first `SELECT`.

It means that in order to apply this logic, you will have to move your `ORDER BY` clause from inside the derived table to the outer level.  That is a good thing though, because you should not rely on any inner `ORDER BY` to propagate to the final set.  When you want to apply a sorting order to the final output, the corresponding `ORDER BY` clause should always be specified at the outermost level of the statement.

Now we have an interesting situation.  If you try using the above `ORDER BY` clause as is, you will get an unexpected result:

``` 
SELECT CASE WHEN Col2 IS NOT NULL THEN Col1 END AS Col1, Col2, cnt, total
FROM
  (
    SELECT Col1, Col2, COUNT(*) AS cnt, SUM(Col3) AS total
    FROM myTable
    GROUP BY Col1, Col2

    UNION ALL

    SELECT Col1, NULL, COUNT(*), SUM(Col3)
    FROM myTable
    GROUP BY Col1

    UNION ALL

    SELECT NULL, NULL, COUNT(*), SUM(Col3)
    FROM myTable
  )
ORDER BY
  CASE WHEN Col1 IS NULL THEN 1 END ASC,
  Col1 ASC,
  CASE WHEN Col2 IS NULL THEN 1 END ASC,
  Col2 ASC
```
| Col1 | Col2 | cnt | total |
| :-----|:-----|:----|:------|
| *null* | *null* | 3 | 11 |
| *null* | *null* | 3 | 15 |
| R1 | C1 | 2 | 7 |
| R1 | C2 | 1 | 4 |
| R2 | C2 | 1 | 4 |
| R2 | C3 | 1 | 8 |
| R2 | C5 | 1 | 3 |
| *null* | *null* | 6 | 26 |
[fiddle](https://dbfiddle.uk/ZeDQSje8?hide=6)

I am not entirely sure what causes it but my guess is that the `Col1` reference in `ORDER BY` may be applied, though not in a very obvious way, to the `Col1` alias defined in the outermost `SELECT` (the calculated column), rather than to the derived table's `Col1`.

Whatever the case may be, you can just ensure that the reference applies to the derived table's column if you assign an alias to the derived table and qualify the column name with that alias, like this:

``` 
SELECT CASE WHEN Col2 IS NOT NULL THEN Col1 END AS Col1, Col2, cnt, total
FROM
  (
    SELECT Col1, Col2, COUNT(*) AS cnt, SUM(Col3) AS total
    FROM myTable
    GROUP BY Col1, Col2

    UNION ALL

    SELECT Col1, NULL, COUNT(*), SUM(Col3)
    FROM myTable
    GROUP BY Col1

    UNION ALL

    SELECT NULL, NULL, COUNT(*), SUM(Col3)
    FROM myTable
  ) AS s
ORDER BY
  CASE WHEN s.Col1 IS NULL THEN 1 END ASC,
  s.Col1 ASC,
  CASE WHEN s.Col2 IS NULL THEN 1 END ASC,
  s.Col2 ASC
```
| Col1 | Col2 | cnt | total |
| :-----|:-----|:----|:------|
| R1 | C1 | 2 | 7 |
| R1 | C2 | 1 | 4 |
| *null* | *null* | 3 | 11 |
| R2 | C2 | 1 | 4 |
| R2 | C3 | 1 | 8 |
| R2 | C5 | 1 | 3 |
| *null* | *null* | 3 | 15 |
| *null* | *null* | 6 | 26 |
[fiddle](https://dbfiddle.uk/F6wW5DNe?hide=6)

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.