Count number of records returned by group by

Question

You can do both in one query using the OVER clause on another COUNT

select
    count(*) RecordsPerGroup,
    COUNT(*) OVER () AS TotalRecords
from temptable
group by column_1, column_2, column_3, column_4

The simplest solution is to use a derived table:

Select Count(*)
From    (
        Select ...
        From TempTable
        Group By column_1, column_2, column_3, column_4
        ) As Z

Another solution is to use a Count Distinct:

Select ...
    , ( Select Count( Distinct column_1, column_2, column_3, column_4 )
        From TempTable ) As CountOfItems
From TempTable
Group By column_1, column_2, column_3, column_4

I know it's rather late, but nobody's suggested this:

select count ( distinct column_1, column_2, column_3, column_4) 
from   temptable

This works in Oracle at least - I don't currently have other databases to test it out on, and I'm not so familiar with T-Sql and MySQL syntax.

Also, I'm not entirely sure whether it's more efficient in the parser to do it this way, or whether everyone else's solution of nesting the select statement is better. But I find this one to be more elegant from a coding perspective.

I was trying to achieve the same without subquery and was able to get the required result as below

SELECT DISTINCT COUNT(*) OVER () AS TotalRecords
FROM temptable
GROUP BY column_1, column_2, column_3, column_4

How about:

SELECT count(column_1)
FROM
    (SELECT * FROM temptable
    GROUP BY column_1, column_2, column_3, column_4) AS Records

A CTE worked for me:

with cte as (
  select 1 col1
  from temptable
  group by column_1
)

select COUNT(col1)
from cte;

In PostgreSQL this works for me:

select count(count.counts) 
from 
    (select count(*) as counts 
     from table 
     group by concept) as count;