Home > Archive > SQL Server Programming > May 2005 > complex query help - count
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
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 'TRIALOFFER%'
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
| |
| Steve Kass 2005-05-31, 9:00 pm |
| Mike,
You have no join condition on the [downloads]
table. Your query is counting every download_id
value because every row of [downloads] is matched
up with each main_id, fname, lname, subscription_ends.
My guess is that you want to add another condition
so that for each main_id, only the corresponding downloads
will be counted, instead of all of them:
where downloads.main_id = m.main_id
If you write this query using INNER JOIN
instead of a comma, and specify join conditions
with ON instead of WHERE, you will be less likely
to make the mistake of inadvertantly introducing a
cross join.
Steve Kass
Drew University
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 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 'TRIALOFFER%'
>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
>
>
>
|
|
|
|
|