If/Else in a query, with group by function

Question

I've these datas:

+-----------+-----------+--------+-------------+--------+
|   Date    |  Data NP  |  Type  |    Desc     | Amount |
+-----------+-----------+--------+-------------+--------+
| 10/1/2017 | 1/1/1970  | Income | invoice 812 | 600000 |
| 10/1/2017 | 1/1/1970  | Income | invoice 813 |  60000 |
| 10/1/2017 | 1/1/1970  | Income | invoice 813 |  50000 |
| 10/1/2017 | 1/1/1970  | Income | invoice 813 |  20000 |
| 10/1/2017 | 1/1/1970  | Income | invoice 815 |  23000 |
| 10/1/2017 | 1/1/1970  | Income | invoice 815 |  28000 |
| 10/1/2017 | 10/6/2017 | Income | invoice 816 |  70000 |
| 10/1/2017 | 10/6/2017 | Income | invoice 816 |  50000 |
| 10/1/2017 | 1/1/1970  | Income | invoice 817 | 140000 |
+-----------+-----------+--------+-------------+--------+

I would like to get a new table where there's only one date, populated with date NP if > of 1/1/1970 otherwise Date. The new table has to be grouped by Desc, so to have the total amount for each invoice.

At the moment this is my formula:

SELECT Col1, Col3, Col4, SUM(Col5) WHERE Col5>0 AND Col1>= date '"&text($A$1,"yyyy-mm-dd")&"' GROUP BY Col4

but I'm stuck as Google spreadsheet throw an error as I mix an aggregation result, SUM(Col5) with other normal results.

Answer 1

To solve the error, you should include all the columns that are not being summarized on the GROUP BY clause:

Try

SELECT Col1, Col3, Col4, SUM(Col5) WHERE Col5>0 AND Col1>= date '"&text($A$1,"yyyy-mm-dd")&"' GROUP BY Col1, Col3, Col4

Regarding the "if", Google Query doesn't include an if clause, so you you could apply the IF by using spreadsheet functions, before or after you do apply the QUERY function.