Hi, Mandeep:
I don't think there is any simple syntax that you are overlooking.
The COUNT( DISTINCT ... ) syntax requires a single expression to be
substituted for the ellipsis. If there were a "functor" in SQL that
could form a unique ordered pair, say (SalesPerson,Department), by
associating the two respective field values, then we could use that to
create the required expression. Unfortunately SQL does not support
this level of abstraction.
A "cheesy" approximation to this (which I have stooped to using on
occasion) would be to exploit the concatenation of the two strings.
Thus:
SELECT COUNT( DISTINCT SalesPerson + Department )
FROM SalesTable
or the more detailed query:
SELECT Item, COUNT( DISTINCT SalesPerson + Department )
FROM SalesTable
GROUP BY Item
would work imperfectly. It would be possible (if unlikely) for the
end of one SalesPerson value to agree with the beginning of a
Department value in such a way that the same concatenated string could
be formed by what ought to be two distinct pairings.
For example, if we had two salespersons:
Jan Kruek
Jan Krueks
and two departments:
Tools
Stools
then (unless SQL Server is set for case sensitivity, which is not the
default), there would be two potential ways to get the same names
combination:
Jan KruekStools
Jan KrueksTools
Of course this is somewhat unlikely. It can also be made essentially
impossible. If we pick a character that doesn't occur in any
department name, for example, if "#" doesn't appear in any value for
Department, then the revised queries:
SELECT COUNT( DISTINCT SalesPerson + '#' + Department )
FROM SalesTable
and:
SELECT Item, COUNT( DISTINCT SalesPerson + '#' + Department )
FROM SalesTable
GROUP BY Item
can be relied upon without exception.
This can be a useful "trick" if one is interested in COUNT's more than
in an actual list of the distinct pairings of SalesPerson and
Department, and your phrasing of your question gives me every reason
to think that this is the case. To some extent the trick depends on
the datatypes of SalesPerson and Department being character strings,
but even when this is not so, as perhaps when a datetime is involved,
it can be made to work by converting to strings.
Of course it's also wise to have a more "SQL standard" approach to
this task in mind, if only for the sake of considering an alternative.
One such approach would be to use a nested subquery as a "derived
table". Not all SQL implementations allow this, but MS SQL Server
2000 does:
SELECT COUNT(*)
FROM (SELECT DISTINCT SalesPerson, Department
FROM SalesTable) AS DerivedTableA
or (to the more detailed counts):
SELECT Item, Count(*)
FROM (SELECT DISTINCT SalesPerson, Department, Item
FROM SalesTable) AS DerivedTableB
GROUP BY Item
Finally, having treated this with some generality, notice that in the
important special case that the fields (SalesPerson, Department, Item)
formed a unique compound key on SalesTable, i.e. if their combinations
uniquely identify the records in that table, then it is unnecessary to
use a derived table. One can then write simply:
SELECT Count(*) FROM SalesTable
and:
SELECT Item, Count(*) FROM SalesTable
GROUP BY Item
While I feel confident this uniqueness would _not_ apply in your
original case (because of the inclusion of the SalesDate field), it
could be made to apply by forming a VIEW on the SalesTable, esp. one
which returns unique combinations of SalesPerson, Department, and
Item. For example:
CREATE VIEW DistinctSalesTable AS
SELECT SalesPerson, Department, Item, Sum(Qty)
FROM SalesTable
GROUP BY SalesPerson, Department, Item
Once the appropriate VIEW is created, the simpler syntax can be
invoked.
regards, mathtalk-ga
Search Strategy
Mostly personal experience, however I used MS SQL Server Books Online
to confirm the syntax (as well as SQL Query Analyzer to test out
suggestions)
Further Resources
Here's a short SQL script I developed in testing my answer:
CREATE TABLE SalesTable (
SalesPerson varchar(25) not Null,
Department varchar(12) not Null,
Item char(5) not Null,
Qty integer not Null,
SalesDate datetime not Null,
CONSTRAINT PK_Sales PRIMARY KEY (
SalesPerson,
Department,
Item,
SalesDate
)
)
SELECT COUNT(*)
FROM (SELECT DISTINCT SalesPerson, Department
FROM SalesTable) AS DerivedTableA
SELECT Item, Count(*)
FROM (SELECT DISTINCT SalesPerson, Department, Item
FROM SalesTable) AS DerivedTableB
GROUP BY Item |
,
0 Comments
)