3

I'm trying to count the number of rows that have distinct values in both of the columns "a" and "b" in my Sybase ISQL 9 database.

What I means is, the following dataset will produce the answer "4":

a b


1 9
2 9
3 8
3 7
2 9
3 7

Something like the following syntax would be nice:

SELECT COUNT(DISTINCT a, b) FROM MyTable

But this doesn't work.

I do have a solution:

SELECT COUNT(*) FROM
  (SELECT a, b 
     FROM MyTable 
    WHERE c = 'foo'
    GROUP BY a, b) SubTable

But I was wondering if there is a neater way of constructing this query?

Ian Hickman
  • 311
  • 1
  • 4
  • 11

2 Answers2

6

How about:

SELECT COUNT(*)
FROM (SELECT DISTINCT a, b FROM MyTable)

For more information on why this can't be done in a simpler way (besides concatenating strings as noted in a different answer), you can refer to the this Google Answers post: Sql Distinct Count.

Roee Adler
  • 33,434
  • 32
  • 105
  • 133
3

You could concatenate a and b together into 1 string like this (TSQL, hopefully something very similar in Sybase:

SELECT COUNT(DISTINCT(STR(a) + ',' + STR(b)))
FROM @YourTable
AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • 2
    This wouldn't work if a or b are string columns containing commas – soulmerge Jul 29 '09 at 10:05
  • True, I was assuming they were integers. If they are strings, then any delimiter could be used...something like "{RANDOM_DELIM}". Just a case of deciding on a suitable delimiter I guess – AdaTheDev Jul 29 '09 at 10:23
  • The data that I'm interested in are stored as ints so this method works - I've just tested it! – Ian Hickman Jul 29 '09 at 10:26
  • While you're at it, test performance as well. I think this solution will be slower than what you originally tried it with. – Tomalak Jul 29 '09 at 11:17