navigation
a webmaster learning community
     Home    Register     Search      Help      Login    
FrontPage Alternative
Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums
 

Advanced search
Recent Posts

 Todays Posts
 Most Active posts
 Posts since last visit
 My Recent Posts
 Mark posts read

 

Mixing DISTINCT and COUNT in an SQL

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP and Database >> Mixing DISTINCT and COUNT in an SQL 		Page: [1]
 
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		Mixing DISTINCT and COUNT in an SQL - 9/14/2002 13:35:11   
To all,

Does anybody know the correct SQL syntax for mixing the words DISTINCT and COUNT?

I already tried these 3 (below)... They do NOT work:

SELECT DISTINCT Count(' SubCategory' ) AS [Total] FROM SubCategories

SELECT DISTINCT SubCategory Count(' SubCategory' ) AS [Total] FROM SubCategories

SELECT DISTINCT SubCategory FROM SubCategories Count(' SubCategory' ) AS [Total]

Thanks in advance... :)
LLL

_____________________________

kvl

 

Posts: 61
Joined: 5/30/2002
From:
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/14/2002 14:03:30   
Try SELECT COUNT(DISTINCT SUBCATEGORY) AS TOTAL FROM SUBCATEGORIES. This is how it works in SQL2000.

(in reply to Long Island Lune)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/14/2002 16:26:09   
Hi LLL,

Try this:

SELECT DISTINCT books.Title, books.Publisher, Count(books.Comment) AS CountOfComment
FROM books
GROUP BY books.Title, books.Publisher;



Regards
Hisham

(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/14/2002 19:04:31   
kvl,

Thanks for the sample. I tried it with no luck. I guess it does not work in Access. Too bad. If I remove the word DISTINCT, it works fine, but as soon as I put in back in, it does not work.
Thanks :)

Hisham,
Thanks for the sample. I tried it with no luck.
Thanks :)

This is not my lucky day. I tried the Internet but could not find any sites that had examples with DISTINCT and COUNT together in an SQL statement. It' s like these two words together in Access are SQL Taboo.

Maybe a nested SQL... I don' t know.

MY Search goes on.
Thanks guys.
LLL:)

_____________________________


(in reply to Long Island Lune)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/15/2002 1:32:59   
Hi LLL,

Send me you .mdb file, tell me what you want and you will get it.

Or Explain in details, what fields are involved, and what is the result that you need, relax and leave it to me.

The sample I sent you is working fine with me and displaying one record with the count of that record.

Regards
Hisham

< Message edited by hhammash -- 9/14/2002 1:33:49 AM >

(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/15/2002 13:00:06   
Hisham,

Thank you for your generous offer. Unfortunatley, I cannot send the database. It has data my client would not like shared. But I can give you the basic details:

Here is the original SQL that didn' t work:
SELECT DISTINCT Count(' SubCategory' ) AS [Total] FROM SubCategories

The table name is: SubCategories
The field name is: SubCategory.

I need to know how many different subcategories are in this table. When a user enters a subcategory it may be a duplicate of a subcategory that another user entered prior.

Example:

Shoes
Toys
Microwave
Shoes
Clock
Glue
Shirt
Shoes
Chair
Shoes

There are 10 categories but only 7 DISTINCT categories in the list. I need to count up all the different categories that are in this table. That' s basically it. And I can' t understand why something relatively simple is such a problem. :)

This reminds me of when you helped s2bn1 with the <= date problem. It worked on his computer, your computer but not mine!!! I' m beginning to think my computer is from Jupiter!!! :)

My equipment is: Win98, FP2000, Access 2000, PWS.

Thanks for ALL your help. :)
I appreciate it. :)
LLL




_____________________________


(in reply to Long Island Lune)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/15/2002 13:37:21   
Hi LLL,

This is the code:
SELECT DISTINCT Subcategories.Subcategory
FROM Subcategories ORDER BY Subcategories.Subcategory;


I made a table Called Subscategories with one field Subcategory and entered all the ten Categories that you listed and queried 7 only.

Check this:
http://www.fmhs.uaeu.ac.ae/nml/LLL.asp

I don' t know why did you use count?

Best regards
Hisham

Note: I know that the info might be confidential, you could just copy the table structure, input 10 dummy fields and send it to me. When I work on your database itself, I can guarantee the results. Well, I hope the above works.:)

< Message edited by hhammash -- 9/14/2002 1:38:14 PM >

(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/15/2002 13:55:38   
Hisham,

Thanks for the code. :) I went to the site. I saw the first table, then the second. It looked great. But what you did I already have. I think you misunderstood. My problem was not in determining them or displaying them, my need was for a count. Actually this is for a Stats page that I am working on for a client. He needs a page that he can go to see statistics for the site. I need to create 35 different statistics. I got 34 done. I used Count() in many other queries with no problems. It' s just this one that does not work. Because DISTINCT and Count() do not seem to " work and play well with each other" in MS-Access. :)

I can determine the distinct entires and display them. But I need a count of how many of the entires are distinct. I just bought SQL Server 2000 but don' t have the time to install it, just like FP 2002. I' m always to busy. So I' m trying to do this in Access. KVL already gave me the solution in SQL Server, but I' m not willing to drop Access yet and make the transformation to 2000. Who knows how many worms will pop out of that can!!!

So, do you think you can get a count from a DRW concening this???
I appreciate all the work you have done on my part. :)
Thank you very much. :)
Mark - LLL

_____________________________


(in reply to Long Island Lune)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/15/2002 13:58:40   
Hi LLL,

Go to the site again, I was updating it when you was viewing it.

the code you want is:

SELECT DISTINCT Subcategories.Subcategory, Count(Subcategories.Subcategory) AS CountOfSubcategory
FROM Subcategories
GROUP BY Subcategories.Subcategory;

Regards
Hisham

(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/15/2002 14:18:13   
Hisham,

You almost did it!!! Your method added up how many times each subcategory was entered, not the total number of different subcategories. I needed a grand total of different subcategories. I' m almost there. In my original example (ABOVE), it needs to return a value of 7.

Thanks for all your help. :)
I appreciate it.
Mark

_____________________________


(in reply to Long Island Lune)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/15/2002 14:20:12   
No worries friend,

Consider it done.


Hisham

(in reply to Long Island Lune)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/15/2002 15:21:06   
Hi LLL,

Please check this:

http://www.fmhs.uaeu.ac.ae/nml/LLL.asp

First table shows 7 records, second table shows 8 records because each one is from a different table. Don' t worry, it is not a mistake

Hisham

< Message edited by hhammash -- 9/14/2002 3:22:34 PM >

(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/15/2002 18:17:47   
Hisham,

I just went to your site and saw your two examples. Terrific. The first one is the moneymaker for me. The 7 total was the last entry after the query. That will work. What was the SQL you used for the first example???
Thanks my friend.
LLL

_____________________________


(in reply to Long Island Lune)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/16/2002 0:23:49   
Hi Friend,

Here is how you do the first example:
First you have to create your DRW to show only Distinct Records using this SQL:
SELECT DISTINCT Subcategories.Subcategory, Count(Subcategories.Subcategory) AS CountOfSubcategory
FROM Subcategories GROUP BY Subcategories.Subcategory;


Or the SQL you have which you mentioned that it is giving you Distinct results. Then:

1- Open your Database Results page in Normal View
2- Click in the first column
3- Table/Insert/Column
4- 1 column to the left
5- Type a heading for the column, example " Serial"
6- Click inside the new field, and type " LLL"
7- Highlight LLL in the new field then click HTML view
8- You will see now a highlighted area in HTML view
9- Type the following code in palce of the highlight
<%=FP_iCount+1%>

Save the page and run your DRW, you will see the serial numbers starting with 1 and ending with the number of returned records.

Regards
Hisham

ps: Didn' t you like the second one?:)


< Message edited by hhammash -- 9/15/2002 12:35:15 AM >

(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/16/2002 1:00:56   
Hisham,

Thanks for the instructions. I will do it now. Yes, I liked the second one too. Absolutely. But for the job, the first one is better. Thanks Hisham.
Mark :)

_____________________________


(in reply to Long Island Lune)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/16/2002 1:11:42   
Hi Mark,

You are WELCOME

Hisham

(in reply to Long Island Lune)
stu

 

Posts: 129
From: doylestown,pa
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/16/2002 14:19:47   
Hisham,
Just to let you know. Your advise also helped me with a similar issue I was having.
Thank You,

stu

(in reply to Long Island Lune)
Long Island Lune

 

Posts: 2340
Joined: 6/8/2002
From: New York
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/16/2002 14:22:33   
stu,

Hisham IS " the man" ...
He' s great with date queries too.

_____________________________


(in reply to Long Island Lune)
hhammash

 

Posts: 1064
Joined: 8/19/2002
Status: offline

  		RE: Mixing DISTINCT and COUNT in an SQL - 9/16/2002 16:27:27   
Hi,

Thank you stu, you are welcome.

LLL, thank you very much for you continuous support and appreciation. Your replies are always rewarding. Thanks a lot friend.


Best regards
Hisham

(in reply to Long Island Lune)
Page:   [1]

All Forums >> Web Development >> ASP and Database >> Mixing DISTINCT and COUNT in an SQL 		Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Back to top

  Microsoft Corporation in no way endorses or is affiliated with "OutFront."
  OutFront is published and edited by Thomas Brunt.
  440 Great Circle Dr., St. Matthews, SC 29135.
  © 2008 All Rights Reserved
  Click here to view our privacy statement.