I have the following table that I'm trying to find the total count for each unique name element. The problem is, other: 1 and other: Null are supposed to be different elements, but my query is grouping them together. The issue I'm running into is that null is not being counted as a row. But when I use count(*), other: 1 and other: Null are grouped together.

The goal is to return:

[{Other: 1}, {Other: 1}, {Facebook: 2}, {Google: 1}]

I've tried:

SELECT name, count(id) 
FROM table 
WHERE id IS NOT NULL 
AND groupId='${id}' 
GROUP BY name 
UNION 
SELECT name, count(*) 
FROM table 
WHERE id IS NULL 
AND groupId ='${id}' 
GROUP BY name

And:

SELECT name, count(id)
FROM table
WHERE id='${id}' 
GROUP BY name

How would I get the desired return value above?

CodePudding user response：

You seem to want one result row per name and ID, so don't group by name only, but by name and ID:

SELECT name, COUNT(*) 
FROM table 
WHERE groupId = '${id}' 
GROUP BY name, id
ORDER BY name, id;

As you don't want to show the ID in your result, omit it from the select clause.

CodePudding user response：

You're not grouping by Id

I think what you need is

SELECT name, count(*)
FROM table
WHERE Groupid='${id}' 
GROUP BY name, Id

CodePudding user response：

Use a flag column that checks if id is null to group by:

SELECT name, 
       id IS NULL null_flag, 
       COUNT(*) count 
FROM tablename 
WHERE groupId = '${id}'  
GROUP BY name, null_flag;

Or, if you don't want it in the results:

SELECT name, COUNT(*) count 
FROM tablename 
WHERE groupId = '${id}'  
GROUP BY name, id IS NULL; 

See the demo.