CODEWITHSUNDEEP
sqlpostgresql
Ahmed
Ahmed

Reputation: 41

Add a summary row with totals for each clause

I can't figure out how to make this sql select statement, I use PostgreSQL database. Here are my tables :

enter image description here

I want to add an extra row show the count of customer by gender:

enter image description here

Could this be done?

Upvotes: 2

Views: 216

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

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

SQL Fiddle DEMO

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

Lukasz Szozda
Lukasz Szozda

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:

enter image description here

Keep in mind that idcust is probably INT so to get Total in that column you have to cast to text first.

Upvotes: 3

Related Questions