help debug groupby - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help debug groupby

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-15 : 05:28:26

alter PROCEDURE [dbo].[spsite]
@fromdate datetime,
@todate datetime

AS
BEGIN

select site as sitecode,
count(id) as t from an where mydate >=@fromdate and mydate<=@todate,
count(id) as p from an where p=1 and mydatestage1 >=@fromdate and mydatestage1<=@todate

group by site

what am i doing wrong?

I get incorrect syntax near ,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-15 : 08:58:24

You cant have two where clauses like this in a select statement. probably you need this.

alter PROCEDURE [dbo].[spsite]
@fromdate datetime,
@todate datetime

AS
BEGIN

select site as sitecode, 
count(case when mydate >=@fromdate and mydate<=@todate then id else null end) as t,
count(case when p=1 and mydatestage1 >=@fromdate and mydatestage1<=@todate then id else null end) as p 
from an 
where (mydate >=@fromdate and mydate<=@todate)
or (p=1 and mydatestage1 >=@fromdate and mydatestage1<=@todate)
group by site

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-16 : 02:45:49

but my problem is i need multiple where clauses.

is this possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 02:49:57

quote:
Originally posted by esthera

but my problem is i need multiple where clauses.

is this possible?

Why do you need multiple where clauses? If your intention is to use multiple filter conditions just seperate them by OR/AND clause in single WHERE statement. If not, please explain whats your scenario and what you're trying to achieve with some sample data and your expected output.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-16 : 07:52:29

I want to run this stored procedure to get different counts

if you see in my exmaple - the first select is selecting based on mydate and the second where is based on mydatestage1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 08:12:14

quote:
Originally posted by esthera

I want to run this stored procedure to get different counts

if you see in my exmaple - the first select is selecting based on mydate and the second where is based on mydatestage1

i've kwpt them as it is in my sngle where seperated by OR so that it returns the results of both your filter. Then i've applied them inside case to get your respective counts. I guess which is exactly what you want.Any reason why you think this is wrong? or are you not getting correct values? Can you explain what discrepancy you're observing?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-16 : 08:16:42

ok i think i see now - i'll have to test

so to add more counts to this I would just have to add more times the or

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 08:35:18

quote:
Originally posted by esthera

ok i think i see now - i'll have to test

so to add more counts to this I would just have to add more times the or

That depends on your other conditions. you also need to add them inside case when of count() function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources