Getting count over() value after distinct executed

Question

I have a query such as

select
distinct
t.*,
t.name + ' ' + t.lastname as customername
count(t.id) over() as count
from 
table t
inner join othertable o
on t.id = o.tableid
where t.blah = 'aaa'

The problem is that the count over() calculates the results before the distinct is executed and therefore returns an incorrect value.

I could remove the distinct and use group by but this will give the count for each group and I want the sum of those values.

I could do a subquery but the problem is this query is getting built up inside an application so I'd have to do some string manipulation to add the where clause to the subquery and main sql body.

Is there a way to get the count show the results after the distinct is executed?

Thanks

Answer 1

This should solve the problem

select count(v.col_a) over() as count,
       v.*
from (select distinct t.col_a, t.col_b, ... --all columns you need
             t.name + ' ' + t.lastname as customername
      from table t
           inner join othertable o on t.id = o.tableid
      where t.blah = 'aaa') v

Answer 2

Use group by, but use the correct expression:

select t.*,
       t.name + ' ' + t.lastname as customername
       sum(count(t.id)) over() as total_count
from table t inner join
     othertable o
     on t.id = o.tableid
where t.blah = 'aaa'
group by . . .