Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

Please consider the following: 

IF OBJECT_ID ('tempdb..#Customer') IS NOT NULL
   DROP TABLE #Customer;

CREATE TABLE #Customer
(
   CustomerKey    INT IDENTITY (1, 1) NOT NULL
  ,CustomerNum    INT NOT NULL
  ,CustomerName   VARCHAR (25) NOT NULL
  ,Planet         VARCHAR (25) NOT NULL
)
GO

INSERT INTO #Customer (CustomerNum, CustomerName, Planet)
VALUES  (1, 'Anakin Skywalker', 'Tatooine')
      , (2, 'Yoda', 'Coruscant')
      , (3, 'Obi-Wan Kenobi', 'Coruscant')
      , (4, 'Luke Skywalker', 'Tatooine')
      , (4, 'Luke Skywalker', 'Tatooine')
      , (4, 'Luke Skywalker', 'Bespin')
      , (4, 'Luke Skywalker', 'Bespin')
      , (4, 'Luke Skywalker', 'Endor')
      , (4, 'Luke Skywalker', 'Tatooine')
      , (4, 'Luke Skywalker', 'Kashyyyk');

Notice that there are a total of 10 records. I know that I can get the list of distinct combinations of CustomerName and PLanet eith either of the following two queries. 

SELECT DISTINCT CustomerName, Planet FROM #Customer;

SELECT CustomerName, Planet FROM #Customer
GROUP BY CustomerName, Planet;

However, what I'd like is a simple way to get just the count of those values, not the values themselves. I'd like a way that's quick to type, but also performant. I know I could load the values into a CTE, Temp Table, Table Variable, or Sub Query, and then count the records. Is there a better way to accomplish this?

share|improve this question
 
Why is "quick to type" a requirement? How often are you writing these queries by hand? Can you tell us what's more important: that the query is efficient, or that the query is "quick to type"? –  Aaron Bertrand Jun 7 '13 at 16:34
1  
I've added a couple of other query variations to @ypercube's SQLFiddle. You should test those and then you can decide which one you like best based on performance and/or the strain on your typing fingers. –  Aaron Bertrand Jun 7 '13 at 16:40
 
possible duplicate of SQL - how to count unique combination of columns –  Chronial Jul 10 '13 at 17:14
add comment

2 Answers

up vote 5 down vote accepted

This will work in 2005:

SELECT COUNT(*) AS cnt
FROM
 ( SELECT 1 AS d
   FROM  Customer
   GROUP BY Customername, Planet
 ) AS t ;

Tested in SQL-Fiddle. An index on (CustomerName, Planet) would be used, see the query plan (for 2012 version):

Query Plan

The simplest to think, "get all distinct values in a subquery, then count" , yiields the same identical plan:

SELECT COUNT(*) AS cnt
FROM
 ( SELECT DISTINCT Customername, Planet
   FROM  Customer
 ) AS t ;

And also the one (thanx to @Aaron Bertrand) using ranking function ROW_NUMBER() (not sure if it will be efficient in 2005 version, too, but you can test): 

SELECT COUNT(*) AS cnt
FROM 
  (SELECT rn = ROW_NUMBER() 
          OVER (PARTITION BY CustomerName, Planet 
                ORDER BY CustomerName) 
   FROM Customer) AS x 
WHERE rn = 1 ;

There are also other ways to write this (one even without subquery, thanx to @Mikael Erksson!) but not as efficient.

share|improve this answer
 
Two more ways to skin the cat in this updated SQL Fiddle. –  Andriy M Jun 10 '13 at 5:29
 
@AndriyM Thanx Andriy! –  ypercube Jun 10 '13 at 5:30
add comment
up vote 1 down vote

The subquery/CTE method is the "right" way to do it.

A quick (in terms of typing but not necessarily performance) and dirty way is:

select count(distinct customername+'###'+Planet)
from #Customer;

The '###' is to separate the values so you don't get accidental collisions.

share|improve this answer
 
Terrible performance-wise though. And also you have to be sure that # can't naturally appear in your data, –  Aaron Bertrand Jun 7 '13 at 16:28
 
@AaronBertrand . . . The OP specifically says not to use a CTE/subquery etc: "I know I could load the values into a CTE, Temp Table, Table Variable, or Sub Query, and then count the records." Can you think of another method? –  Gordon Linoff Jun 7 '13 at 16:30
 
I realize that. My comment is not just for the OP (who also stated that "performant" - not a word - is a requirement). Your answers says "a quick and dirty way" which might lead people to believe it performs the same as, or maybe even better than, a subquery. You should include a disclaimer that it will actually be slower than the subquery / CTE approaches. I think the requirement to not use a subquery / CTE is an artificial one anyway. –  Aaron Bertrand Jun 7 '13 at 16:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.