Beginners SQL Query i cant suse out

HI,
I have what should be a simple query , but I just dont seem to have the head
for it :
Tables :
Tables
TableID
TableName
OpenedTime
Covers
CoverID
CoverNumber
TableID_FK
The query is to return back the number of times the table was opened in a
given period and how many covers there were also for this given period. What
I have so far that is wrong :
SELECT COUNT(Tables.TableID), COUNT(Covers.CoverNumber)
FROM Table_Archive INNER JOIN
Covers_Archive ON Table_Archive.TableArchiveID =
Covers_Archive.TableArchiveID_FK
ThanksI think I sorted using "DISTINCT"
"Jon Vaughan" <jonnyvaughan@.hotmail.com> wrote in message
news:urHPf.24197$Gh4.17452@.fe07.news.easynews.com...
> HI,
> I have what should be a simple query , but I just dont seem to have the
> head for it :
> Tables :
> Tables
> TableID
> TableName
> OpenedTime
> Covers
> CoverID
> CoverNumber
> TableID_FK
> The query is to return back the number of times the table was opened in a
> given period and how many covers there were also for this given period.
> What I have so far that is wrong :
> SELECT COUNT(Tables.TableID), COUNT(Covers.CoverNumber)
> FROM Table_Archive INNER JOIN
> Covers_Archive ON Table_Archive.TableArchiveID =
> Covers_Archive.TableArchiveID_FK
>
> Thanks
>|||I'm not at all sure I understand what you are asking for, but try
changing the COUNTs to COUNT(distinct):
COUNT(distinct Tables.TableID)
COUNT(distinct Covers.CoverNumber)
Roy Harvey
Beacon Falls, CT
On Wed, 08 Mar 2006 20:49:31 GMT, "Jon Vaughan"
<jonnyvaughan@.hotmail.com> wrote:

>HI,
>I have what should be a simple query , but I just dont seem to have the hea
d
>for it :
>Tables :
>Tables
>TableID
>TableName
>OpenedTime
>Covers
>CoverID
>CoverNumber
>TableID_FK
>The query is to return back the number of times the table was opened in a
>given period and how many covers there were also for this given period. Wha
t
>I have so far that is wrong :
>SELECT COUNT(Tables.TableID), COUNT(Covers.CoverNumber)
>FROM Table_Archive INNER JOIN
> Covers_Archive ON Table_Archive.TableArchiveID =
>Covers_Archive.TableArchiveID_FK
>
>Thanks
>|||First of all, your table name in your list don't appear to match what's
in the SQL code. So I made some Create Table/Insert statements to have
something to work with. Plus, you never explained what data types the
columns are supposed to be. So I made it up. :) Those are:
Create Table Table_Archive (TableID int, TableName nvarchar(20),
OpenedTime DateTime)
Create Table Covers_Archive (CoverID int, CoverNumber int, TableID_FK
int)
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (1,
'Test1', '3/8/06 1 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (1,
'Test1', '3/8/06 3 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (1,
'Test1', '3/8/06 7 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (1,
'Test1', '3/9/06 1 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (2,
'Test2', '3/8/06 1 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (2,
'Test2', '3/8/06 3 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (2,
'Test2', '3/8/06 7 pm')
Insert Into Table_Archive (TableID,TableName,OpenedTime) Values (2,
'Test2', '3/9/06 1 pm')
Insert Into Covers_Archive (CoverID,CoverNumber,TableID_FK) Values
(1,99,1)
Insert Into Covers_Archive (CoverID,CoverNumber,TableID_FK) Values
(2,99,1)
Insert Into Covers_Archive (CoverID,CoverNumber,TableID_FK) Values
(3,67,2)
Insert Into Covers_Archive (CoverID,CoverNumber,TableID_FK) Values
(4,88,2)
Using the above, the SQL below should give you what I t hink you are
looking for.
SELECT T.TableName,
T.OpenCount,
C.CoverCount
FROM
(SELECT TableName,
TableID,
COUNT(TableID) OpenCount
FROM Table_Archive
GROUP BYTableName,TableID) T
JOIN
(SELECT TableID_FK,
Count(DISTINCT CoverNumber) CoverCount
FROM Covers_Archive
GROUP BY TableID_FK) C
ON T.TableID = C. TableID_FK