Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002
    Posts
    3

    Unhappy Unanswered: Smart way to count the number of TWO distinct fields??

    I'm trying to find out the total number of rows with the distinct values from TWO fields. This works:

    SELECT DISTINCT Field1, Field2 FROM Table
    SET @Nr = @@ROWCOUNT

    But there MUST be a more clever method! COUNT() doesn't work because it can only take ONE distinct field, and that goes for SUM() also. GROUP BY didn't cut it either. Any ideas?

  2. #2
    Join Date
    Mar 2002
    Posts
    3

    Smile

    I've now received two solutions!!

    SELECT @Nr = COUNT(*) FROM
    (SELECT DISTINCT Field1, Field2 FROM Table) AS Nr

    or

    SELECT @Nr = COUNT(DISTINCT (Field1+ CAST(Field2 AS VARCHAR(20))) ) FROM Table

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •