How can I get the count in SQL?

SQL DISTINCT COUNT

• Ok this question should be relatively easily for someone who knows their SQL. It has to do with COUNT and DISTINCT I'm using Microsoft SQL Server. All right I have a table that looks like so: SalesTable SalesPerson Department Item Qty Price SaleDate Eric Chun VideoEq 01001 2 15.00 7/24/03 Eric Chun AudioEq 15646 1 45.00 7/23/03 Fred Chin Books 87894 2 15.00 7/23/03 Eric Chu Books 15489 3 55.00 7/23/03 Ricky Lal Tools 87965 1 5.00 7/23/03 ........................... If I run the following SQL Query SELECT DISTINCT SalesPerson From SalesTable I get SalesPerson Eric Chun Fred Chin Ricky Lal Louis Marc Mandy Brewer If I run the following SQL Query SELECT COUNT(DISTINCT SalesPerson) AS MyCount From SalesTable I get MyCount 5 If I run the following SQL Query SELECT DISTINCT SalesPerson, Department From SalesTable I get SalesPerson Department Eric Chun AudioEq Eric Chun VideoEq Eric Chun Books Fred Chin Books Fred Chin Computers Ricky Lal Tools Ricky Lal VideoEq Louis Marc VideoEq Louis Marc AudioEq Mandy Brewer Computers What I want is a count of the previous QUERY, however SELECT COUNT (DISTINCT SalesPerson, Department) AS MyCount From SalesTable Does not work. I can not just run the Query and then count my results because eventually I will be using a GROUP BY to get several counts i.e. SELECT COUNT( DISTINCT Department ) As DepCount From SalesTable Group By SalesPerson SalesPerson DepCount Eric Chu 3 Fred Chin 2 Ricky Lal 2 Louis Marc 2 Mandy Brewer 1 So the eventual goal would be to run a query like: SELECT COUNT( DISTINCT SalesPerson, Department ) As MyCount From SalesTable Group By Item To give the different number of sales people who have sold an item, but it should count a person selling the same item in multiple departments as multiple people. Sooooooooo Can I even do this? I beleive i should be able to, perhaps its some special syntax that is needed. Thanks, Mandeep

• Answer:

  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