Reputation: 41
I can't figure out how to make this sql select statement, I use PostgreSQL
database.
Here are my tables :
I want to add an extra row show the count of customer by gender:
Could this be done?
Upvotes: 2
Views: 216
Reputation: 48207
For version before a 9.5 you can create an aditional column group_id
and as always be carefull about data type so doesnt mix text and numbers.
NOTE: But consider for this kind of things you usually return both SELECT
separated and join them on the Front End so you can apply proper format/styling
SELECT idcust::text, name, gender
FROM
(
SELECT 2*fkGender as group_id,
idcust::text, name, gender
FROM customers c
JOIN gender g
ON c.fkGender = g.idGender
UNION ALL
SELECT 2*fkGender + 1 as group_id,
'Total'::text as idcust,
COUNT(*)::text as name,
'' as gender
FROM customers c
GROUP BY group_id, fkGender
) T
ORDER BY group_id, idcust
OUTPUT
| idcust | name | gender |
|--------|-------|--------|
| 1 | bob | male |
| 2 | alice | male |
| Total | 2 | |
| 3 | mikel | female |
| Total | 1 | |
Upvotes: 0
Reputation: 176324
You could use GROUP BY GROUPING SETS
(Postgresql 9.5+
):
CREATE TABLE customers(idcust INT, name VARCHAR(100), fkGender INT);
INSERT INTO customers(idcust, name, fkGender)
SELECT 1, 'bob', 3 UNION ALL SELECT 2, 'alice', 3 UNION ALL SELECT 3, 'mikel', 4;
CREATE TABLE gender(IdGender INT, gender VARCHAR(100));
INSERT INTO gender(IdGender, gender) VALUES (3, 'male'),(4,'female');
-------------------------------------------------------------------------------------
SELECT CASE WHEN idcust::text IS NULL THEN 'Total' ELSE idcust::text END AS IdCust,
CASE WHEN name IS NOT NULL THEN name ELSE COUNT(*)::text END AS name,
CASE WHEN idcust::text IS NULL THEN '' ELSE gender END AS genderName
FROM customers c
JOIN gender g
ON c.fkGender = g.idGender
GROUP BY GROUPING SETS((idcust, name, gender), (gender))
ORDER BY gender DESC, idcust;
Output:
Keep in mind that idcust
is probably INT
so to get Total
in that column you have to cast to text first.
Upvotes: 3