Problem with SELECT COUNT DISTINCT...
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=16291
Printed Date: 29 March 2026 at 12:03pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: Problem with SELECT COUNT DISTINCT...
Posted By: theSCIENTIST
Subject: Problem with SELECT COUNT DISTINCT...
Date Posted: 22 August 2005 at 2:15pm
Right, I've been turning my head with this one, and can't figure how to do it.
Scenario:
I'm SELECTing DISTINCT from a table, it returns records fine, but I need to COUNT how many it returns, I can count in the recorset loop doing a c = c + 1 at the end I have my count, however, in this loop I have an array that needs to be populated at the same time, this array needs to be dynamically sized before being filled, so I can't just pre array(20), as it may return more that that, giving it a high value it's not good programming, so I need to know the COUNT from the DISTINCT records returned before I go into the loop, so I can [ Redim array(count) ] and get it sized perfectly.
Question:
Does anyone know how to do a COUNT in the same statement as a normal SELECT DISTINCT ... ?
I'm using ASP and MySQL, not sure if MSSQL can use COUNT, but I'm also willing to hear from all of you that may have an idea on how to do this with MSSQL, as I can get ideas from it.
Thanks.
-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com
|
Replies:
Posted By: theSCIENTIST
Date Posted: 23 August 2005 at 3:53am
Ok, I found the solution, it turns out one can use [ SELECT COUNT(DISTINCT fld_Whatever) AS count FROM tbl_Whatever; ], funny thing thou, I tried this before in many different combinations and it was always erroring out.
I then asked here because I thought and was really convinced, one couln't use the COUNT with the DISTINCT, anyway my fault.
-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com
|
Posted By: Phat
Date Posted: 23 August 2005 at 4:27am
Can't you use Redim Preserve?
Not sure how good it is on memory though.
-------------
http://buildit.sitesell.com/sitebuildithome.html - Get a website that sells
|
Posted By: theSCIENTIST
Date Posted: 23 August 2005 at 6:28am
I don't want to preserve anything, I just want to set my array from the start with the correct size of what it will hold.
Redim Preserve is used if you want to resize the array but retain any data already in it, which is not the case here.
Yeah, Redim Preserve has a somewhat slow performance, due to the nature of the operation, but nothing to worry about on a fast or moderaly used server, unless the server is very slow/old or the data to be preserved is extenssive and the array multi-dimensional, but overall can be used without any noticable impact.
-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com
|
|