MySQL Union to make two columns-CodePudding

Ideally, the code below should output the amount of Males in one column and the amount of Females in another. There is no error but it only creates a column for males and puts the amount for both males and females under its column.

SELECT COUNT(Gender) AS Males FROM Customers 
JOIN Rents ON Rents.Customers_Cid= Customers.Cid
JOIN Cars ON Cars.CarId=Rents.Cars_CarId 
WHERE Make='Dodge' AND Gender='M'
UNION 
SELECT COUNT(Gender) AS Females FROM Customers 
JOIN Rents ON Rents.Customers_Cid= Customers.Cid
JOIN Cars ON Cars.CarId=Rents.Cars_CarId 
WHERE Make='Dodge' AND Gender='F';

CodePudding user response：

Use conditional aggregation with a single pass query:

SELECT SUM(cu.Gender = 'M') AS Males,
       SUM(cu.Gender = 'F') AS Females
FROM Customers cu
INNER JOIN Rents r ON r.Customers_Cid = cu.Cid
INNER JOIN Cars ON c.CarId = r.Cars_CarId 
WHERE c.Make = 'Dodge';

To make your union approach work, you could just select a computed column for the gender, e.g.

SELECT COUNT(Gender) AS Num, 'M' AS Gender
...
UNION ALL
SELECT COUNT(Gender), 'F'