Professional Web Applications Themes

COUnting Distinct Rows - Microsoft SQL / MS SQL Server

I am attempting to figure out how to count the number os distinct rows in a table. I have tablex with columns DATEIN and SESSIONID where SESSIONID can have multiple entries for the same SESSIONID. I only want the row count between two dates in DATEIN ( not a problme) AND not include any duplicates of SESSIONID. I have tried - SELECT DISTINCT COUNT(SESSIONID) as xRows FROM Tablex WHERE DATEIN BETWEEN '8/14/2003 00:00:00 AM' and '8/14/2003 11:59:59 PM' GROUP BY SESSIONID And I get a row for each SESSIONID. All I want is the total row count for the date ...

  1. #1

    Default COUnting Distinct Rows



    I am attempting to figure out how to count the number os distinct rows
    in a table.

    I have tablex with columns DATEIN and SESSIONID where SESSIONID can
    have multiple entries for the same SESSIONID.

    I only want the row count between two dates in DATEIN ( not a problme)
    AND not include any duplicates of SESSIONID.

    I have tried -

    SELECT DISTINCT COUNT(SESSIONID) as xRows FROM Tablex
    WHERE DATEIN BETWEEN '8/14/2003 00:00:00 AM' and '8/14/2003 11:59:59
    PM'
    GROUP BY SESSIONID

    And I get a row for each SESSIONID. All I want is the total row count
    for the date range without and where SESSIONID have multiple entries
    just considered as one row.

    I must be doing something wrong. WHat may it be ?

    Jon Spartan
    Jon Guest

  2. #2

    Default Re: COUnting Distinct Rows

    COUNT(DISTINCT column) is something different that DISTINCT COUNT(column) -
    check

    USE Northwind
    GO
    SELECT COUNT(DISTINCT CustomerID)
    FROM Orders
    SELECT DISTINCT COUNT(CustomerID)
    FROM Orders

    --
    Dejan Sarka, SQL Server MVP
    FAQ from Neil & others at: http://www.sqlserverfaq.com
    Please reply only to the newsgroups.
    PASS - the definitive, global community
    for SQL Server professionals - http://www.sqlpass.org

    "Jon Spartan" <net> wrote in message
    news:com... 


    Dejan Guest

  3. #3

    Default Re: COUnting Distinct Rows

    Here are the results of the last suggestion


    SELECT DISTINCT COUNT(SESSIONID) as xRows FROM tablex
    WHERE DATEIN BETWEEN '8/14/2003 00:00:00 AM' and '8/14/2003 11:59:59
    PM'
    GROUP BY SESSIONID

    Sample Return -

    xRows
    -----------
    1
    2
    3
    4

    (4 row(s) affected)


    SELECT COUNT(DISTINCT SESSIONID) as xRows FROM Tablex
    WHERE DATEIN BETWEEN '8/14/2003 00:00:00 AM' and '8/14/2003 11:59:59
    PM'
    GROUP BY SESSIONID

    Sample Return

    xRows
    -----------
    1
    1
    1
    1
    1
    1


    What I am looking to find is just the total number of rows between the
    date range without with any duplicate SESSIONID rows one counted once
    ( or as one row). So I owuld just have one number xRows = 456

    JonS



    On Sat, 16 Aug 2003 09:44:46 -0400, Jon Spartan <net>
    wrote:
     

    Jon Guest

  4. #4

    Default Re: COUnting Distinct Rows

    Hi Jon,

    Leave of the GROUP BY clause from your second query and it will work fine:
    SELECT COUNT(DISTINCT SESSIONID) as xRows FROM Tablex
    WHERE DATEIN BETWEEN '8/14/2003 00:00:00 AM' and '8/14/2003 11:59:59
    PM'

    Jacco

    "Jon Spartan" <net> wrote in message
    news:com... 
    >[/ref]


    Jacco Guest

  5. #5

    Default Re: COUnting Distinct Rows

    select count(*) from
    (select distinct <column list> from your_table)

    "Jacco Schalkwijk" <co.uk> wrote in message
    news:phx.gbl... 
    > >[/ref]
    >
    >[/ref]


    The Guest

Similar Threads

  1. counting rows
    By WOLFcfm in forum Coldfusion Database Access
    Replies: 7
    Last Post: December 26th, 09:23 PM
  2. Counting HTML Rows
    By TGuthrie in forum Macromedia Dynamic HTML
    Replies: 1
    Last Post: November 14th, 05:30 PM
  3. problem with distinct rows
    By tony in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: March 8th, 01:46 PM
  4. count of distinct rows
    By priya in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 19th, 08:27 AM
  5. Counting rows in mysql
    By Matt Schroeder in forum PHP Development
    Replies: 3
    Last Post: July 18th, 05:30 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139