sql query - 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 2000 Forums

SQL Server Development (2000)

sql query

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-15 : 06:04:26

Hi,
The following query does not work becasueI would like to get the count of the FileID i.e. count(faTemp.FileID)
I know in the group by I have to place all the fields that I am selecting except the one that I am counting.
Could you see if you can make the group by to work please?
P.S. State is not a field (I have just made it up to name the column)

Thanks

select
faTemp.UserID,
faTemp.FileID,
count(faTemp.FileID),
State =
case
when ',' + @ActionID + ',' like '%,' + convert(varchar(100),fa.ActionID) + ',%' then 'GO'
else 'No Go'
end
from
#tblFileActivityTemp as faTemp inner join tblFileActivity as fa on faTemp.FileID = fa.FileID
group by
faTemp.UserID,
faTemp.FileID
????????????
order by
faTemp.UserID,
faTemp.FileID

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-15 : 06:08:20

DO you mean you want th enumber of records in the grouop? The number of different faTemp.FileID's will always be one.
If so put in count(*) instead.
STate is a constant so is not affected by the group by.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-15 : 06:12:30

Hi,
For example without the group by I get the following data.

FileID State

49027214 GO
49027214 No Go
49027214 No Go
49027215 GO
49027215 No Go
49027215 No Go
49027218 GO

But I would like to get:
FileID Count State
49027214 1 No Go
49027214 2 Go
49027215 1 GO
49027215 2 No Go

etc...
Thanks

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-15 : 06:13:58

Sorry the result that I am after should be:
FileID Count State
49027214 1 Go
49027214 2 NO Go
49027215 1 GO
49027215 2 No Go

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-15 : 06:47:02

Sorry - state isn't a constant
select
faTemp.UserID,
faTemp.FileID,
count(*),
State =
case
when ',' + @ActionID + ',' like '%,' + convert(varchar(100),fa.ActionID) + ',%' then 'GO'
else 'No Go'
end
from
#tblFileActivityTemp as faTemp inner join tblFileActivity as fa on faTemp.FileID = fa.FileID
group by
faTemp.UserID,
faTemp.FileID ,
case
when ',' + @ActionID + ',' like '%,' + convert(varchar(100),fa.ActionID) + ',%' then 'GO'
else 'No Go'
end
order by
faTemp.UserID,
faTemp.FileID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-15 : 07:05:50

It works. Well done and thanks.

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-15 : 10:07:33

Hello again,
Now this is the query that I am using.
The result is:
UserID FileID File Count State
USKARC 67409389 1 Go
USKARC 67409389 3 No Go
USKARC 67409391 1 Go
USKARC 67409391 3 No Go
USKARC 67409300 1 No Go

Could you please let me kow how I can achieve the sql so that I get the following result? and bare in mind that if there is only a No Go for a fileid I would like to show it

UserID FileID File Count State
USKARC 67409389 1 Go
USKARC 67409391 1 Go
USKARC 67409300 1 No Go

Thanks

select
faTemp.UserID,
faTemp.FileID,
--count(*) as 'File Count',
State =
case
when ',' + @ActionID + ',' like '%,' + convert(varchar(100),fa.ActionID) + ',%' then 'Go'
else 'No Go'
end
from
#tblFileActivityTemp as faTemp inner join tblFileActivity as fa on faTemp.FileID = fa.FileID
group by
faTemp.UserID,
faTemp.FileID,
case
when ',' + @ActionID + ',' like '%,' + convert(varchar(100),fa.ActionID) + ',%' then 'Go'
else 'No Go'
end
order by
faTemp.UserID,
faTemp.FileID

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-02-17 : 04:08:24

Hi,
The following query does not return any data whereas if I user F.Extension = @Extension it does work.
The problem is that I would like to pass several parameters for the extension.
Thanks

WHERE
',' + @Extension + ',' like '%,' + F.Extension + ',%'
AND U.ValueCentreID = @ValueCentreID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources