SQL Grand Total Column using Over()

Question

I would like to use grand total using over() function however it causes problems when the count() = 0. It actually adds them to the total. The real count is 138 not 141. How can I fix this with keeping the counts() = 0 rows?

select
count(*) cnt,
sum(count(*)) over () grand_total,
count(*)/cast(sum(count(*)) over () as float) percentage
from 
some_table

Data (6 rows returned)

4, 141, .03
116, 141, .82
18, 141, .13
0, 141, .01
0, 141, .01
0, 141, .01

Answer 1

I like puzzles with only partial information (feels kind of like every day actually), so I'll give this a shot. Ok, over is interesting function because it lets you specify your grouping for an aggregate outside of group by, however you have both kinds of aggregates, an over() aggregate and an aggregate without an over function. I'm going to assume the first column is a count of something (I'll call it sales), and the group is on a category (I'll call it product). With that assumption your sql ends up looking like:

Select ProductID
  , count(1) as UnitsSold
  , sum(count(1)) over () as TotalSales
  , cast(count(1) as money)/cast(sum(count(1)) over () as money)
From SomeTable
group by ProductID

A word to the wise, never ever use float unless you have a mission critical reason to do so, float is an approximation, which can cause all sorts of issues. A quick and dirty replacement I use for percentages is money, defaults to four decimal places, which is more than enough for most business needs for a percentage.

Anyway, continuing my hunch, I'm guessing that we are looking at a left outer join of a products table to a sales table (or some other such many to one relationship), so we have some products without sales that are getting counted, which is why you think the total should be less than you are getting from the count. Just to get that on "paper":

Select a.ProductID
  , count(1) as UnitsSold
  , sum(count(1)) over () as TotalSales
  , cast(count(1) as money)/cast(sum(count(1)) over () as money)
From SomeTable_Products a
  left outer join SomeTable_Sales b
group by a.ProductID

If this is the case, you are not counting the right things is your problem. It can be fixed by changing it to:

Select a.ProductID
  , count(b.SalesID) as UnitsSold
  , sum(count(b.SalesID)) over () as TotalSales
  , cast(count(b.SalesID) as money)/cast(sum(count(b.SalesID)) over () as money)
From SomeTable_Products a
  left outer join SomeTable_Sales b
group by a.ProductID

With b.SalesID being the PK of the sales table (and therefore being unique). Hope I guessed right, and hope that helps.