MariaDB – GROUP BY Excluding Empty Strings

group bymariadb

I would like to group by email. Sometimes email is missing in records, which then should not be grouped.

How can I create an exception for empty strings when grouping?

CREATE TABLE example (
  id INT NOT NULL AUTO_INCREMENT,
  fullname VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  something VARCHAR(255) NOT NULL
);
INSERT INTO example (id, fullname, email, something) VALUES
  (1, 'Name 1', 'name1@example.com', 'Something 1'),
  (2, 'Name 2', '', 'Something 2'),
  (3, 'Name 1', 'name1@example.com', 'Something 3'),
  (4, 'Name 3', 'name3@example.com', 'Something 4'),
  (5, 'Name 3', 'name3@example.com', 'Something 5'),
  (6, 'Name 4', '', 'Something 6');

As a result, I would like:

SELECT fullname, email FROM example GROUP BY email [...];
+----------+-------------------+
| fullname | email             |
+----------+-------------------+
| Name 2   |                   |
| Name 4   |                   |
| Name 1   | name1@example.com |
| Name 3   | name3@example.com |
+----------+-------------------+

Best Answer

You can use UNION and use it to get the wanted result.

Instead of WHERE email = '' you can use WHERE email IS NULL, if the column doesn't have '' as text

CREATE TABLE example (
  id INT NOT NULL AUTO_INCREMENT Primary KEY,
  fullname VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  something VARCHAR(255) NOT NULL
);

INSERT INTO example (id, fullname, email, something) VALUES
  (1, 'Name 1', 'name1@example.com', 'Something 1'),
  (2, 'Name 2', '', 'Something 2'),
  (3, 'Name 1', 'name1@example.com', 'Something 3'),
  (4, 'Name 3', 'name3@example.com', 'Something 4'),
  (5, 'Name 3', 'name3@example.com', 'Something 5'),
  (6, 'Name 4', '', 'Something 6');

SELECT GROUP_CONCAT(DISTINCT fullname), email 
FROM example 
WHERE email <> ''
GROUP BY email 
UNION
SELECT fullname, email 
FROM example 
WHERE email = ''
ORDER BY email;

GROUP_CONCAT(DISTINCT fullname) | email            
:------------------------------ | :----------------
Name 2                          |                  
Name 4                          |                  
Name 1                          | name1@example.com
Name 3                          | name3@example.com

SELECT GROUP_CONCAT(DISTINCT fullname), email 
FROM example 
GROUP BY email 

GROUP_CONCAT(DISTINCT fullname) | email            
:------------------------------ | :----------------
Name 2,Name 4                   |                  
Name 1                          | name1@example.com
Name 3                          | name3@example.com

db<>fiddle here

Related Question