I am implementing this table,after apply pivot I get this form. How can I add "Total" columns which is going to calculate the sum of all counts

SELECT
ifnull(jiraissue.Assignee,'Unassigned') as 
Assignee,ifnull(DATE_FORMAT(DUEDATE,'%M/%Y'),'No Planned'),COUNT(jiraissue.ID)
FROM jiraissue
INNER JOIN issuetype
ON jiraissue.issuetype = issuetype.ID
INNER JOIN issuestatus
ON jiraissue.issuestatus = issuestatus.ID
WHERE
issuestatus.pname = 'In Progress' AND
issuetype.pname in ('Minor Enhancement', 'Root Cause Analysis', 'Support Demand 
Request')
group by Assignee,(DATE_FORMAT(DUEDATE,'%M/%Y'))

There's an open feature request on GitHub: Column and row table totals · Issue #5308 · metabase/metabase · GitHub

Until then ... there's ...

The dashboard LEGO brick way

Here's what you maybe might consider to be a super hacky hack - and maybe not applicable in your situation. But anyway I'll share the Idea. It's basically to solve to puzzle by answering it through a few different questions and the putting it all together in a dashboard:


The above dashboard is BTW put together without needing to do any native SQL at all on the Orders table in the Metabase Sample Dataset -- just using pure Metabase GUI Query Builder stuff. So anybody should be able to replicate it to exercise their Metabase skills. :wink:

I’ll leave it as an exercise for you to add totals in the bottom of the pivot table as well.

I guess to apply the same hack as I did above with your SQL it's just a matter of not breaking out by columns - and putting the SQL in a new question and put it next to your pivot as I did above.

The build it all in SQL way

I'm sure someone stronger in SQL than me will walk in here shortly how to do it all in one SQL JOINing those two queries if that's what you want.

1 Like

If you are using Postgress SQL you could use ROLLUP in one of the columns in group by and use coalesce in the columns name to create the ‘Total Name’ .i.e

select
coalesce( date, ‘total’) as occurrence_date,
column_category,
column_values,
from
table
groupy
ROLLUP((occurence_date))

Tell me if it works, I can build it for you

1 Like