count problem

Hi.
I have a table containing two groups and a detail area.
I want to add a counter near the second group area.
It should start from 1 and increment by one as second group value changes.(1, 2, 3, ...)
I tried to use count, countRows .... but they didn't work.
I don't want to learn total count of group value.
How can I do this?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=489400&SiteID=1

Take a look at this post and see if this can help you out! You may have to make some changes, but this might be a start.

If you think this could help, and are unsure about how you could get it to work I brainstormed along this line...

This assumes that you will be doing all your orderring and selection criteria in your SQL statements.

Select your group into a temporary table counting the "detail" rows (this will be your groups). Then join the temp table into your dataset. If all is orderred properly, you should get a dataset that contains your records and your group number in a column.

Like I said, this is just a brain storm. I believe it would work, I just haven't tried it. I do know that the post I've linked to contains some of my code that I tested and verified to work. I have had others tell me it works for them as well. Let me know what you think, and if you need me to expand upon my brainstorm.

Good Luck!

Curtis

|||I have looked at this link lots of times but it didn't work.
You wrote about temp table method.
How can I create a temp table and how can I use it?|||

I'm working on something similiar right now. I'll have a description and a sample before long, unless someone else beats me to the punch.

Curtis

|||

In the group header, you can use a running value based on the CountDistinct function:

=RunningValue(Fields!Id.Value, CountDistinct, Nothing)

Explanation:
The first argument in the running value should be identical to your grouping expression.
The second argument specifies that the CountDistinct function should be used during the calculation.
The third argument specifies that the running value should never reset in your table. If you want it to reset e.g. based on the outer group scope, then specify the outer group name as "reset"-scope.

-- Robert

|||

RunningValue(Fields!Id.Value, CountDistinct, Nothing)

I tried using this. My situation is, I have groups with sub-groups. Some of my sub-groups are not distinct. So something like this may work if every group is distinct, but how do we take in account non-distinct groups? Changing CountDistinct to Count counts all the detail rows, and that's not what I want. I just want a running count of ALL my sub-groups.

This is why I am going the route that I brain stormed. Unless there is an easier way to do it?

Thanks

Curtis

|||

In that case, you will need to look into the custom aggregation approach, similar to the sample in this blog article: http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

-- Robert

|||

Robert Bruckner MSFT wrote:

In the group header, you can use a running value based on the CountDistinct function:

=RunningValue(Fields!Id.Value, CountDistinct, Nothing)

Thanks Robert!
It worked successfully.

count of 'groups' returned by a statement using a GROUP BY clause

Hi all,
I have a real tricky one here that should seem very simple to do but for the
life in me I cannot work out how to do it. My problem is as follows:-
The following query returns 2 rows:-
SELECT @.@.rowcount as numofgroups FROM [TransactionFile]
WHERE [ProductID] = 820 and type = 10
GROUP BY [Ref], [Sell]
Results:
numofgroups
--
2
2
the problem is I dont want 2 rows being returned. I want a single row
returned with the value 2. ie) the number of 'groups'. The data can be
summarized as shown below:-
Ref | Sell
--
a | 1.1
a | 1.1
a | 1.3
As you can see there are 'two' groups where the ref AND sell are the same.
How can i write a query that will simply return the value '2'.
Many TIA,
Leigh.Try
SELECT DISTINCT @.@.rowcount as numofgroups FROM [TransactionFile]
WHERE [ProductID] = 820 and type = 10
GROUP BY [Ref], [Sell]
-
"Leigh" wrote:

> Hi all,
> I have a real tricky one here that should seem very simple to do but for t
he
> life in me I cannot work out how to do it. My problem is as follows:-
> The following query returns 2 rows:-
> SELECT @.@.rowcount as numofgroups FROM [TransactionFile]
> WHERE [ProductID] = 820 and type = 10
> GROUP BY [Ref], [Sell]
> Results:
> numofgroups
> --
> 2
> 2
> the problem is I dont want 2 rows being returned. I want a single row
> returned with the value 2. ie) the number of 'groups'. The data can be
> summarized as shown below:-
> Ref | Sell
> --
> a | 1.1
> a | 1.1
> a | 1.3
> As you can see there are 'two' groups where the ref AND sell are the same.
> How can i write a query that will simply return the value '2'.
> Many TIA,
> Leigh.|||Hi, Leigh
You can use this query:
SELECT COUNT(*) FROM (
SELECT Ref, Sell FROM [TransactionFile]
WHERE ProductID = 820 and type = 10
GROUP BY Ref, Sell
) x
which is equivalent to:
SELECT COUNT(*) FROM (
SELECT DISTINCT Ref, Sell FROM [TransactionFile]
WHERE ProductID = 820 and type = 10
) x
Razvansql