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