I have a large query that gets pasted into an Excel sheet. The query
sorts the results by company name and then location (traditional stock-
keeping report). The user can then change various numbers in the
sheet, writing down stock for instance, and a series of formulas
updates the numbers for that row.

Now I'm trying to add subtotaling for the two "groupings" (company,
location). Since the user can change the numbers within the groups,
the subtotals have to be formulas that update. I would like to put
these formulas in as explicit rows, like "=SUM(Q1:Q37)" instead of
using SUMIF or one these more expensive formulas. To make this easy,
it would be nice if every row of the result set also contained the
start and end position of it's own group, or alternately the number of
rows in the group.

So, is there an easy way that sql can generate these sorts of "row
numbers within a group"? I seem to recall reading about this recently,
but I can't find the key words when I'm looking now...

Maury

On SQL Server 2005 you can use COUNT(*) OVER. You can partition by the
grouping columns and get count for the groups:

COUNT(*) OVER(PARTITION BY company, location)

You can also add ranking within a group using ROW_NUMBER() or
RANK()/DENSE_RANK().

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Jun 17, 10:31am, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> On SQL Server 2005 you can use COUNT(*) OVER. You can partition by the
> grouping columns and get count for the groups:
>
> COUNT(*) OVER(PARTITION BY company, location)

Great, thanks! That's exactly what I was looking for.

Maury