Adding extra column and row to count sum() of each table row and column in Mysql-CodePudding

Currently I have 2 tables in mysql which are listings table and agents table. The listings table holds all statuses, refno and an agent_id that operates with that particular product. Now the names for these agents are stored in my agents table. So to reference the agents name with a particular listing, I use inner join agents table and check all entries where agent_id = id. This gives me the correct output.

Now I am retrieving the count of all statuses of each product with their respective agents using:

select SUM(t.status = 'D' ) AS draft,
    SUM(t.status = 'N' ) AS unpublish,
    SUM(t.status = 'Y' ) AS publish,
    SUM(t.status = 'S' ) AS sold,
    SUM(t.status = 'T' ) AS let, c.name
    FROM listings t INNER JOIN agents c ON t.agent_id = c.id
    GROUP BY t.agent_id

But now I want an extra row and column that counts total of the data in each row and each column. Something like this:

Link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f1f7ce90a0562b75f62641272ccf1a93

CodePudding user response：

Simply sum together the 5 current boolean expressions:

SELECT
    COALESCE(c.name, 'Total') AS name,
    SUM(t.status = 'D') AS draft,
    SUM(t.status = 'N') AS unpublish,
    SUM(t.status = 'Y') AS publish,
    SUM(t.status = 'S') AS sold,
    SUM(t.status = 'T') AS let,
    SUM(t.status = 'D')   SUM(t.status = 'N')   SUM(t.status = 'Y')  
    SUM(t.status = 'S')   SUM(t.status = 'T') AS Total
FROM listings t 
INNER JOIN agents c
    ON t.agent_id = c.id
GROUP BY
    t.agent_id
WITH ROLLUP;