complex query help - count

Hi,
I am trying to add an aggregate function to the query below but I am not
able to get the intended results. I want to get the count to return total
downloads by each user but my query returns the total downloads by all users
.
Current Output
1,ttt,rrr,6/1/3005,30
2,ddd,jjj,5/31/2005,30
3,ppp,yyy,5/20/2005,30
Desired Output
1,ttt,rrr,6/1/3005,15
2,ddd,jjj,5/31/2005,5
3,ppp,yyy,5/20/2005,10
QUERY:
select distinct spl. [main_id],fname,lname,subscription_ends,
count(download_id)
from
main m,
subscribers spl,
downloads
where
m.main_id = spl.main_id
and
spl.confnum like 'T12%'
and
subscription_ends > = Getdate()
group by
spl.[main_id],
fname,lname,subscription_ends
order by
subscription_ends DESC
CREATE TABLE [dbo].[Main] (
[main_id] [int] IDENTITY (1, 1) NOT NULL ,
[fname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
GO
CREATE TABLE [dbo].[subscribers] (
[main_id] [numeric](18, 0) NOT NULL ,
[confnum] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[subscription_starts] [datetime] NOT NULL
)
GO
CREATE TABLE [dbo].[Downloads] (
[main_id] [numeric](18, 0) NOT NULL ,
[download_id] [numeric](18, 0) NOT NULL
)
GOMike,
Try this,
select distinct spl. [main_id],fname,lname,subscription_ends,
count(main_id)
from
main m,
subscribers spl,
downloads
where
m.main_id = spl.main_id
and
spl.confnum like 'T12%'
and
subscription_ends > = Getdate()
and downloads.main_id = m.main_id
group by
spl.[main_id],
fname,lname,subscription_ends
order by
subscription_ends DESC
Thanks
"Mike" wrote:

> Hi,
> I am trying to add an aggregate function to the query below but I am not
> able to get the intended results. I want to get the count to return total
> downloads by each user but my query returns the total downloads by all use
rs.
> Current Output
> 1,ttt,rrr,6/1/3005,30
> 2,ddd,jjj,5/31/2005,30
> 3,ppp,yyy,5/20/2005,30
>
> Desired Output
> 1,ttt,rrr,6/1/3005,15
> 2,ddd,jjj,5/31/2005,5
> 3,ppp,yyy,5/20/2005,10
> QUERY:
> select distinct spl. [main_id],fname,lname,subscription_ends,
> count(download_id)
> from
> main m,
> subscribers spl,
> downloads
> where
> m.main_id = spl.main_id
> and
> spl.confnum like 'T12%'
> and
> subscription_ends > = Getdate()
> group by
> spl.[main_id],
> fname,lname,subscription_ends
> order by
> subscription_ends DESC
> CREATE TABLE [dbo].[Main] (
> [main_id] [int] IDENTITY (1, 1) NOT NULL ,
> [fname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [lname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> GO
>
> CREATE TABLE [dbo].[subscribers] (
> [main_id] [numeric](18, 0) NOT NULL ,
> [confnum] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [subscription_starts] [datetime] NOT NULL
> )
> GO
> CREATE TABLE [dbo].[Downloads] (
> [main_id] [numeric](18, 0) NOT NULL ,
> [download_id] [numeric](18, 0) NOT NULL
> )
> GO
>