Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2006
    Posts
    28

    Unanswered: basic sql question

    I want to get a count of distinct rows from my table (sql server 2005).

    This works:
    select distinct b, a from first

    and this works:
    select count(distinct a) from first

    but this doesn't:
    select count(distinct a, b) from first
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ','.

    What am I doing wrong?!?

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm not sure what you mean, and neither is SQL Server.

    My first guess would be:
    Code:
    SELECT Count(DISTINCT a), Count(DISTINCT b)
       FROM first
    -PatP

  3. #3
    Join Date
    Jul 2006
    Posts
    28
    First, thanks for the response.

    I want a count all distinct combinations of a & b, not separate counts for each. Basically I'm trying to find out if I have any duplicate combinations of (a & b) and how many.

    -Amy

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Code:
    Select col1,col2,count(*)
    FROM mytable
    Group by col1,col2
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    try this

    Code:
    create table #temp(a int, b int);
     
    insert into #temp (a,b)
    select 1,1
    union
    select 1,2
    union
    select 2,1;
     
    insert into #temp (a,b)
    select 1,1;
     
    select * from #temp;
     
    select a,b,count(*) as rcount
    from #temp
    group by a,b
    compute sum(count(*));
     
    drop table #temp;

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jul 2006
    Posts
    28
    Thanks Tom, that more or less gives me what I want. I still don't understand why
    select distinct a, b from first
    works, and
    select count(distinct a, b) from first
    doesn't work though. I thought the count() operator just evaluated the number of rows returned by the query within the parenthesis..?

    IE: doesn't "select count(*) from mytable" just return the number of rows from "select * from mytable"?

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The DISTINCT clause cannot take multiple individual columns.

    Here is my go at it:
    Code:
    select count(*) from (select distinct a, b from first) Subquery
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if they are strings...

    SELECT COUNT(DISTINCT a+b) FROM first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Bzzzzzz....thanks for playing Rudy. Better luck next time:
    Code:
    ColA	ColB
    ABC	DEFG
    ABCD	EFG
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jul 2006
    Posts
    28
    Blindman -

    distinct seems to take multiple columns:

    select distinct a, b from first
    a b
    ----------- --------------------------------------------------
    1 aaaa
    2 bbbb
    3 this is a test
    4 this is a test
    4 xxx

    I'd either get a syntax error, or I'd get a (distinct a), but neither of those is what I'm seeing...

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by r937
    if they are strings...

    SELECT COUNT(DISTINCT a+b) FROM first

    SELECT COUNT(DISTINCT a + 'some really improbable string here to keep blindman quiet' + b) FROM first
    Last edited by jezemine; 06-05-08 at 21:05.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by amy7756
    Blindman -

    distinct seems to take multiple columns:

    select distinct a, b from first
    a b
    ----------- --------------------------------------------------
    1 aaaa
    2 bbbb
    3 this is a test
    4 this is a test
    4 xxx
    In this example you are using distinct against the entire result set. But when used to modify the COUNT() function, like this COUNT(DISTINCT ...) you can only include one field.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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