Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2008
    Posts
    2

    Unanswered: COUNT Function with one table

    Hi,

    This is probably pretty simple to most here as I'm pretty much a newbie when it comes to databases. I'm using one table and within that table there are 4 colums:

    Web Application/Web page/IP Address/Hit Date

    There aren't spaces of course but just wanted it easier to read. Anyway, I'm trying to use this by Just showing the Web Application/The Number of View to that Application/ The date that the views were recorded. So I tried this:

    Select WebApp, COUNT(DISTINCT IPAddress) AS NumberOfHits
    FROM webhits
    GROUP BY webhits.WebApp

    Since the same IP address can be in the application several times by the different pages I wanted the distinct count. The works just to display the count but when I add another field to have the date that these were shown I get an error:

    Select WebApp, COUNT(DISTINCT IPAddress) AS NumberOfHits, HitDate
    FROM webhits
    GROUP BY webhits.WebApp

    Msg 8120, Level 16, State 1, Line 1
    Column 'webhits.HitDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


    So I tried this:

    Select WebApp, COUNT(DISTINCT IPAddress) AS NumberOfHits, HitDate
    FROM webhits
    GROUP BY webhits.WebApp, webhits.HitDate

    But that just duplicates the Web Application and the number of hits shows as "1" several times because of all the different dates. I need a total for that date. I'm not sure how to write out the query?

    Thanks for your help in advance!
    Paul

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    show some actual sample data, and show the results you want

    make sure you have same ip different dates, same date different apps, same app different ips, etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2008
    Posts
    2
    Hi r937,

    I actually got my answer from a Database Administrator I was able to get ahold of. It had to do with the date data which had a timestamp that was causing some issues.

    Thanks for the reply!
    Paul

    Here's what worked:

    Select webapp, 'Hit_Date' = convert(varchar(10),hitdate,101), count(distinct(ipaddress)) As Number_Of_Hits from webhits
    group by webapp, convert(varchar(10),hitdate,101)
    order by webapp, Hit_Date DESC

Posting Permissions

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