Complex conditional column when using GROUP BY

Question

I have here two tables: Customer and Company in One-To-One relationship. In other words, Company table has a PK customerid column which is a foreign key that references to the PK id of Customer. This means, that if there's a record in the Company table, then the corresponding customer is a company.

The following is a simplified visual diagram:

Now, when you want to find out how many customers and companies there are in both tables per letter (representing the customer name or the company name), you can execute the following query:

SELECT LEFT(u.name, 1) AS letter, count(*) AS count FROM 
    (
        SELECT name,
            CASE
                WHEN (SELECT customerid FROM company WHERE company.customerid = id) THEN "Company"
                ELSE "Customer"
            END
            AS origin
        FROM customer
        UNION ALL
        SELECT companyname AS name, 'Company' AS origin FROM company
    ) AS u
GROUP BY letter
ORDER BY letter

Then you get the following result set:

However, I still need one more generated column in my result set (e.g. column 'type`), where I can see if there are only customers representing the corresponding letter, or companies, or both.

This means: I need something like this as a result set:

letter | count | type
--------------------------
A        1       Companies
B        3       Both
C        2       Customers
<...>

Is it possible to do it by implementing a conditional column? I have already tried so many different things; it just fails every time.

---

JUST IN CASE

Just in case you need this simplified SQL-seed:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Table structure for table `company`
DROP TABLE IF EXISTS `company`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `company` (
  `customerid` int(10) unsigned NOT NULL,
  `companyname` varchar(45) NOT NULL,
  PRIMARY KEY (`customerid`),
  CONSTRAINT `fk_company$customerid` FOREIGN KEY (`customerid`) REFERENCES `customer` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `company`
LOCK TABLES `company` WRITE;
/*!40000 ALTER TABLE `company` DISABLE KEYS */;
INSERT INTO `company` VALUES (2,'Jane Solutions LLC'),(4,'Disney Company'),(5,'Bad Company');
/*!40000 ALTER TABLE `company` ENABLE KEYS */;
UNLOCK TABLES;

-- Table structure for table `customer`
DROP TABLE IF EXISTS `customer`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `customer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `pseudonym` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `customer`
LOCK TABLES `customer` WRITE;
/*!40000 ALTER TABLE `customer` DISABLE KEYS */;
INSERT INTO `customer` VALUES (1,'John Smith','John (NY)'),(2,'Jane Smith',NULL),(3,'John Smith','John (LA)'),(4,'Tom Smith',NULL),(5,'Alice Smith',NULL),(6,'Bob Smith',NULL),(7,'Benoit Smith',NULL),(8,'Craig Smith',NULL),(9,'Celine Smith',NULL);
/*!40000 ALTER TABLE `customer` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Answer 1

You may use conditional aggregation over your computed origin column to do the check:

SELECT
    LEFT(u.name, 1) AS letter,
    COUNT(*) AS count,
    CASE WHEN COUNT(CASE WHEN origin = 'Company' THEN 1 END) > 0 AND
              COUNT(CASE WHEN origin = 'Customer' THEN 1 END) > 0
         THEN 'Both'
         WHEN COUNT(CASE WHEN origin = 'Company' THEN 1 END) > 0
         THEN 'Companies'
         WHEN COUNT(CASE WHEN origin = 'Customer' THEN 1 END) > 0
         THEN 'Customers' END AS type
FROM 
(
    SELECT name, 'Customer' AS origin FROM customer
    UNION ALL
    SELECT companyname, 'Company'     FROM company
) t
GROUP BY letter
ORDER BY letter;