SitePoint Sponsor |
|
User Tag List
Results 1 to 4 of 4
Thread: query help
-
Jan 18, 2005, 21:34 #1
- 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.
-
Jan 18, 2005, 22:33 #2
- 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!
-
Jan 18, 2005, 23:00 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 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)
-
Jan 19, 2005, 00:31 #4
- 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