Count nodes by property conditional and un-conditional?

[BairDev]

I am using Neo4J 4.2.3.

Suppose a dataset of this structure:

(:UsedMeasure {name: 'Bicycle', confirmed: 'smth'})
(:UsedMeasure {name: 'Bicycle'})
(:UsedMeasure {name: 'Bicycle'})
(:UsedMeasure {name: 'LED', confirmed: 'smth'})
(:UsedMeasure {name: 'LessMeat', confirmed: 'smth'})
(:UsedMeasure {name: 'LessMeat', confirmed: 'smth'})
(:UsedMeasure {name: 'LessMeat'})

Now I want to count the UsedMeasures by name, which is easy. But I also want to count the nodes by name, which have a confirmed string as property (like WHERE m.confirmed IS NOT NULL).

I have no idea how to get this done the right way.

This query for example does not work at all:

MATCH (u:User {isBetUser: true}) WITH u MATCH (u)-->(m:UsedMeasure), (u)-->(mConf:UsedMeasure) WHERE m.confirmed IS NOT NULL RETURN m.name AS name, COUNT(m.name) AS measuresCount, COUNT(mConf.name) AS measConfCount ORDER BY measuresCount DESC;

it gives something like

+----------------------------------------------------+
| name               | measuresCount | measConfCount |
+----------------------------------------------------+
| "Bicycle"          | 11905         | 11905         |
...

So I need some sort of conditional count. There was a filter method, but I did not see it anymore in the docs, right?

Do I need to collect an unwind the UsedMeasure nodes and doing some counting by name in the loop (which seems to be over-complicated to me)?

[ameyasoft]

Try this:

MATCH (a:UsedMeasure) where a.confirmed is not null

//get total count.....
RETURN count(a) as cnt

//count by name.......
RETURN distinct a.name as Name, count(a) as cnt

[BairDev]

Ah, I can combine both:

MATCH (u:User {isBetUser: true})-->(m:UsedMeasure) 
    RETURN
        DISTINCT m.name,
        COUNT(m) AS measuresCount,
        SUM(CASE WHEN m.confirmed IS NULL THEN 0 ELSE 1 END) AS confirmedCount
   ORDER BY measuresCount DESC;