Geek asks and answers

How to add total as last row in my sql?

I am trying to display the string "Total", which will do the sum of the Count column. The following code will contain two columns: "Environment and quantity."

select 
case 
when env is null THEN 'Unknown' 
else env
end,
count(*) as Count
from env_table
group by env
order by env
/

The conclusion I would like:

Windows 200

Linux 120

Total 320

As you can see above, what I would like to do is add a line called “Total” at the end, which essentially will do SUM (count (*)). What is the correct syntax for this? Thank!

+3
source share
4 answers

Use the modifier WITH ROLLUPbefore GROUP BY:

SELECT   IFNULL(env, 'Unknown'),
         COUNT(*) AS Count
FROM     env_table
GROUP BY env WITH ROLLUP
ORDER BY env
+15
source

Maybe something like this?

SELECT   IFNULL(env, 'Unknown'),
         COUNT(*) AS Count
FROM env_table
GROUP BY env
ORDER BY env
UNION ALL
SELECT   null,
         Count(*)
FROM env_table
+3
source 
SELECT env, count 
  FROM (SELECT CASE WHEN env is null THEN 'Unknown' ELSE env END env,
               count(*) count
          FROM env_table
         GROUP BY env
      ORDER BY env)
UNION ALL
SELECT 'Total' env,
       count(*) count
  FROM env_table
0
SELECT   COALESCE(env, 'Total') AS Description,
         COUNT(*) AS Count
FROM     env_table
GROUP BY env WITH ROLLUP
ORDER BY env
0
source

More articles:


All Articles