SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: query help

  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    AUS
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query help

    Greetings Everyone,

    Below is my query that i'm trying to get it to work.

    I want the query to select only the unique rows. In my example, the unique feild is the date field. The result doesnt fetch the unique fields. It simply spits out all fields! what am i doing wrong?

    Dim strSQL2 as String = "select DISTINCT count(*) as rec, convert(char(6),dateEntered,13) as dateEntered from EmailTracker where convert(char(12),dateEntered,13) >= '"& stdate &"' AND convert(char(12),dateEntered,13) <= '"& endate &"' group by convert(char(6),dateEntered,13)"


    The fields are used in a chart control. I just want to track unique clicks and draw up a chart on the dates. any help is very much appreciatd. Thanks.

  2. #2
    SitePoint Member
    Join Date
    Jan 2005
    Location
    AUS
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok that problem was solved by creating a view and then selecting distinct rows from that view.

    Now, the count function wont work rite.

    I added another field, username, to the query. now when i draw the chart, because of the group by clause, it takes each count with a matched username as a new count and draws separate lines for them. what they really should do is, they should combine all the matched counts in to one line.

    For ex, if there are three unique clicks from three users

    bob 11/01/05
    george 11/01/05
    adam 11/01/05

    I want the query to count them all as unique clicks and draw one single line on the chart marking the count. Now it paints a line for each one of the records. It was doing right before i changed the query to include username in. I assume this is due to the group by clause which groups both username and date field. is there anyways to get around this problem?

    below is my modified query.

    Dim strSQL2 as String = "select DISTINCT count(*) as rec, convert(char(6),dateEntered,13) as dateEntered, ekey from trackerview where dateEntered >= '"& stdate &"' AND dateEntered <= '"& endate &"' group by convert(char(6),dateEntered,13), ekey"

    i hope i havent confused no one. pls help if u can asap... much appreciated!

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, you have to remove ekay from your query
    Code:
    select count(*) as rec
         , convert(char(6),dateEntered,13) as dateEntered
      from trackerview 
     where dateEntered >= '"& stdate &"' 
       AND dateEntered <= '"& endate &"' 
    group 
        by convert(char(6),dateEntered,13)
    FYI, style 13 used with char(6) will give you totals for dd mon without the year -- is that what you want?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Jan 2005
    Location
    AUS
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yep, i wanted that style so that on the x axsis i can display just the date and month.

    i solved the problem. thanks for ur help anyways Rudy.


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
  •