I have a database table on a SQL server called "Pages" with the following fields:

- GUID (Text)
- Requested (Date / Time)
- Page (Text)
- Source (Text)
- Referrer (Text)
- IP (Text)

I need a SQL Select statement which will show me each distinct Source,
with the Count of the Source, but it must be only for unique
(distinct) records from the GUID field.

I can get it to work in Access using 2 queries, but have a mental
block in getting this to work in SQL.

If any of this is unclear, I would be happy to try and clarify.

select GUID, source, count(*) from pages group by GUID, source

Thanks very much - that might just work.

Feeling particularly dumb now .....

How do I reference the Count of the Source?  I was previously using:

rs("CountOfSource").

I'm using ASP 3.0.

Thanks again.

To reference the column count in your asp code give a name to the count such as:

select GUID, source, count(*) as CountOfSource from pages group by GUID, source

You can use that name in your code.

Thanks for the last part - it worked fine.  The problem is that the
select statement isn't giving me what I need.

Let's say the table has:

GUID      Source
111       AAA
111       AAA
222       AAA
333       BBB

The output should show AAA Count = 2, BBB Count = 1 (ignoring the
duplicate GUID for the Source AAA).

Hope that helps.

I appreciate any suggestions!

If the redundent data isn't necessary, why not create a unique key for
those two fields?  That way, you can just count by source, and it will
tell you if you are inserting data that's already there.

Good point and probably a good way to do it.  I have a bunch of other
fields in the table and it is very handy to have the source recorded
for each record.

By having it in each record, I can easily sort through the table and
pull out data with that source and other characteristics in the other
fields.  It works well for that.

I'm thinking the answer is a "simple" subquery.  I can get it to work
in Access using a query of a query, but I can't seem to get the syntax
correct in SQL.

If a given GUID only corresponds to one Source, then this should work:

select Source, count(*) as CountOfSource from
  (select distinct GUID, Source from pages) 
group by Source

If there can be two records with the same GUID but different Sources,
then it's not clear to me what you want returned. The above query will
give you an entry under each Source.

I hope I understood correctly - isn't this what you need ?

select count(GUID) as countofdource, source from pages group by source

This counts the total number of GUID values for each source.

count(*) instead of count(GUID) would work as well, but I feel it's
clearer this way.

Yours,
    Guy.

Hi again,

Thanks for the suggestions - it seems like we are getting pretty
close.  I'm really not a pro at all in terms of SQL syntax, so when I
tried the Select statement below, it is throwing an error:  "Incorrect
syntax near the keyword 'group'".

select Source, count(*) as CountOfSource from
  (select distinct GUID, Source from pages) 
group by Source

I am definitely eager to have an answer since my current report is
giving me incorrect data.  Right now, my select statement that I came
up with is not excluding duplicate GUID's so my Source numbers are way
off.

I will happily add a $10 tip for a working answer.  If I need to add
any more details, I would be pleased to do so.


The other suggestion below seems to be giving me the same results that
I am getting using my select statement.  For example, for one of the
source elements, it is showing me 10, whereas it should be 3 because
of eliminating the duplicate GUID's.

"select count(GUID) as countofsource, source from pages group by source"

Thanks again.

I'm sorry - I did mis-understand the first time.. I gaev you the exact
thing you claimed didn't work.

This statement should give out correct results:

select count(distinct GUID) as countofdource, source from pages group by source

BINGO!!

That worked just perfect.  If you would like to convert it to an
answer, I would gladly close out the question and add the tip!

Thanks very much!

Best regards,

DeskGuy

seems like I cannot get paid because I'm not an official google
answers researcher...

Enjoy the gift :)

Bummer, too bad you can't convert to a question and get the money.  I
really do appreciate the help.  I had to go to a meeting this morning
and it was great to have the correct data coming out of the database
(at least before anyone noticed that I had made a mistake previously!)

Thanks again!

Best regards,

DeskGuy

just try this


SELECT GUID, 
COUNT(GUID) AS NumOccurrences
FROM pages
GROUP BY GUID
HAVING ( COUNT(GUID) > 1 )