DeFacto
I have this sqlite query:
```
CREATE TABLE myTable (`Col1` TEXT, `Col2` TEXT, `Col3` INT);
```
```
INSERT INTO myTable (`Col1`, `Col2`, `Col3`) VALUES
('R1', 'C1', 5),
('R2', 'C3', 8),
('R1', 'C1', 2),
('R1', 'C2', 4),
('R2', 'C5', 3),
('R2', 'C2', 4);
```
```
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 |
| R1 | *null* | 3 | 11 |
| R2 | C2 | 1 | 4 |
| R2 | C3 | 1 | 8 |
| R2 | C5 | 1 | 3 |
| R2 | *null* | 3 | 15 |
[fiddle](https://dbfiddle.uk/tIZa1TBR)
Is it possible to replace values of `Col1` with `NULL` where `Col2 = NULL`?
Top Answer
Jack Douglas
Yes, it's easy to do this with the [`case` expression](https://www.sqlite.org/lang_expr.html#the_case_expression)
> A CASE expression serves a role similar to IF-THEN-ELSE in other programming languages.
```
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, cnt, total
);
```
| Col1 | Col2 | cnt | total |
| :-----|:-----|:----|:------|
| R1 | C2 | 1 | 4 |
| R1 | C1 | 2 | 7 |
| *null* | *null* | 3 | 11 |
| R2 | C5 | 1 | 3 |
| R2 | C2 | 1 | 4 |
| R2 | C3 | 1 | 8 |
| *null* | *null* | 3 | 15 |
[fiddle](https://dbfiddle.uk/6qQsiXHJ)
However, with the sample data in your fiddle there is no need to do this as it's much simpler to just select `null` directly in the second aggregate query, as you can still group by a column that doesn't appear in the `select`:
```
SELECT Col1, Col2, COUNT(*) AS cnt, SUM(Col3) AS total FROM myTable GROUP BY Col1, Col2
UNION ALL
SELECT NULL, NULL, COUNT(*), SUM(Col3) FROM myTable
GROUP BY Col1 ORDER BY Col1, cnt, total
```
| Col1 | Col2 | cnt | total |
| :-----|:-----|:----|:------|
| *null* | *null* | 3 | 11 |
| *null* | *null* | 3 | 15 |
| R1 | C2 | 1 | 4 |
| R1 | C1 | 2 | 7 |
| R2 | C5 | 1 | 3 |
| R2 | C2 | 1 | 4 |
| R2 | C3 | 1 | 8 |
[fiddle](https://dbfiddle.uk/VUUOGtnL)