SQL Query to give me a total count of all distinct values-CodePudding

I'm sure this is pretty straightforward but can't get my head round it at all.

In one of my DB tables, I have a column 'partitionDate'. This is populated every time a transaction is logged in to the DB table with the date 11-JAN-2023 for example. So we could have 100 transactions all with the partitionDate of 11-JAN-2023.

I've run a query to give me the total count for each distinct partitionDate

SELECT partitionDate, COUNT (DISTINCT partitionDate)
from tablename

I'm trying to get a grand total at the bottom that shows me all of the totals added up which I guess will be a SUM but I can't work it out!

Thanks

I'm trying to get a grand total at the bottom that shows me all of the totals added up which I guess will be a SUM but I can't work it out!

CodePudding user response：

In T-SQL:

SELECT partitionDate
      ,COUNT (partitionDate) 
from tablename
GROUP BY GROUPING SETS
(
    (partitionDate)
   ,()
)

In MySQL should be something like this:

SELECT partitionDate, COUNT(partitionDate)
FROM tablename
GROUP BY partitionDate WITH ROLLUP;