GROUP BY field is empty

[BaumiCoder]

Hello,
I want to GROUP BY if a field is empty and show the sum of another column for the both groups (empty and none empty)
I try it like this:

TABLE sum(rows.ECTS) AS MySum
FROM outgoing([[#]])
GROUP BY (grade != null)

or

TABLE (grade != null) AS gradeExists, sum(rows.ECTS) AS MySum
FROM outgoing([[#]])
GROUP BY gradeExists

I only get errors like that:

Dataview: Error: 
-- PARSING FAILED --------------------------------------------------

Got the end of the input

Expected one of the following: 

'(', '*' or '/', '+' or '-', '.', '>=' or '<=' or '!=' or '=' or '>' or '<', '[', 'and' or 'or', whitespace

So I want to get a table like that:

Group	MySum
true	60
false	30

I have not much experience with DataView Plugin yet, but I have experiences with SQL. With SQL such kind of I a SELECT-query would work as I expected.

I would be thankful for any help.

[AB1908]

Expressions like those aren't allowed in either group by or in columns defs. Best bet would be to where first and then group by.

[AB1908]

table without id
length(rows)
from
source
where grade
group by grade

Does this work?

[BaumiCoder]

table
sum(rows.ECTS) as MySum
from outgoing([[#]])
where grade
group by grade

Gives me the ECTS sum for each kind of grade (1, 2, 3, 4, 5) and not the ECTS sum of all notes with an grade.

table
length(rows) as MyCount
from outgoing([[#]])
where grade
group by grade

Gives me the count of notes for each kind of grade (1, 2, 3, 4, 5) and not the count of all notes with an grade.

So both is not want I am looking for.

[AB1908]

Oh then remove the group by?

[BaumiCoder]

table
sum(rows.ECTS) as MySum
from outgoing([[#]])
where grade

gives me an table with one line for each note with an grade. The sum column is empty. If I change rows.ECTS to ECTS there is only the ECTS value of each note in the sum column.

Maybe there is really no way to get this in one table, because the DataView Plugin isn't as powerful as a real SQL.

[AB1908]

It's a fun problem. Can you pack a minimal version of this vault and send it over? I could use that as the index to test out some queries. I think I don't fully understand the problem here and some test data would be great.

[blacksmithgu]

You can group on booleans in the beta, where I believe your existing queries should work.

[blacksmithgu]

Ah, specifically, your first query will work - the second won't work since you can't reference variables defined in the TABLE part later in the query. I.e.,

TABLE blah as foo, ...
WHERE blah =

does not work. The blah as foo syntax does not make variables, it just names them in the table.

[BaumiCoder]

I copied the main.js, manifest.json and styles.css in my .obsidian/plugins/dataview folder from the last beta release. With that the first query works fine. Thank you 👍

Only one little flaw now catch my eye. The group column has the ugly caption "grade != null". I want to rename that. I know that I can remove the automatic group column with "WITHOUT ID", but I can't make another column with the same values. I tried this query, but the passed column is always false.

TABLE (grade != null) AS passed, sum(rows.ECTS) AS "ECTS Sum"
FROM outgoing([[#]])
GROUP BY (grade != null)

Is there a may to rename the group column directly (only for this one table)? I tried ID AS passed, but that didn't work.
Should I proved a small test vault? Is this a bug / feature request and I should make a ticket for this problem?

[AB1908]

GROUP BY field as alias should work

[BaumiCoder]

TABLE sum(rows.ECTS) AS "ECTS Sum"
FROM outgoing([[#]])
GROUP BY (grade != null) AS passed

Thank you, that works 👍