use of the count function in sql

December 31, 2023

I have a small doubt, can I show the counted total of the rows instead of what it appears below ,separately. Without using subqueries?

SELECT  COUNT(TEMA),tema
FROM LIBRERIAS 
WHERE TRIM(TEMA )LIKE 'GEO%IA'  AND EJEMPLARES BETWEEN 5 AND 20
GROUP BY tema;

I would like to get the total counted showed only one time. It is possible? Can i achieve this?

>Solution :

You can use SUM OVER to get the total:

SELECT SUM(COUNT(tema)) OVER () AS total, tema
FROM librerias 
WHERE TRIM(tema) LIKE 'GEO%IA'
AND ejemplares BETWEEN 5 AND 20
GROUP BY tema
ORDER BY tema;

A more typical query result would be the result you already got along with a total row (using ROLLUP). Yet another, one single result row showing the count and the temas:

SELECT
  COUNT(tema) AS total,
  LISTAGG(tema, ', ') WITHIN GROUP (ORDER BY tema) as temas
FROM librerias 
WHERE TRIM(tema) LIKE 'GEO%IA'
AND ejemplares BETWEEN 5 AND 20;