Obtaining a count of summary lines in SQL 1

[raze]

Hi there.<br>
<br>
I wonder if someone could help me out.<br>
<br>
I have a table which contains the following fields<br>
<br>
date_time<br>
user_name<br>
page_title<br>
<br>
what I'm trying to do is get a count (just a single number) of all the user_names that have more than one entry for a particular page title?<br>
<br>
The following code gives me the summary list<br>
<br>
------------------------------<br>
SELECT Count(*) <br>
FROM YourTable<br>
WHERE page_title='Title of Page' &lt;... or whatever ...&gt;<br>
GROUP BY user_name<br>
HAVING Count(page_title) &gt; 1;<br>
---------------------------------<br>
<br>
which gives me a list of numbers that match the criteria. What I need to find out, is how many numbers there are in that list .... and here's the tricky part; It has to be all in one statement.<br>
<br>
Can anyone help?<br>
<br>
Thanks.

 

[calahans]

Have you thought of creating a view:<br>
<br>
create viuew tst as <br>
SELECT Count(*) <br>
FROM YourTable<br>
WHERE page_title='Title of Page' &lt;... or whatever ...&gt;<br>
GROUP BY user_name<br>
HAVING Count(page_title) &gt; 1<br>
<br>
<br>
then in your code just run<br>
select count(*) from tst<br>
<br>
OR<br>
<br>
select count (rowsa.recs) from <br>
(SELECT Count(*) as recs <br>
FROM YourTable<br>
WHERE page_title='Title of Page' &lt;... or whatever ...&gt;<br>
GROUP BY user_name<br>
HAVING Count(page_title) &gt; 1) rowsa<br>
<br>
This creates a virtual table called rowsa and then you can select on it.<br>
<br>
HTH<br>
<br>
C

 

[raze]

Thanks! That's brilliant ... and quick!<br>
<br>
Cheers!<br>

 

[bigscouse]

Ingres Version.<br>
<br>
select count (distinct username) <br>
from YourTable a <br>
where exists ( <br>
select * <br>
from YourTable b <br>
where b.username = a.username <br>
and b.date_tine != a.date_time <br>
and b.page_title = a.page_title); <p>Ged Jones<br><a href=mailto:gedejones@hotmail.com>gedejones@hotmail.com</a><br><a href= > </a><br>