A simple query which I don't know how to write!

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)

A simple query which I don't know how to write!

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-07 : 07:16:04

I'm having such query:
Select * from table1 group by col1, col2, col3

I need another column which counts col3 for each col1

any idea please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:18:28

then dont use group by just use partition

select *,count(col3) over (partition by col1) as count3 from table1

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-07 : 07:23:18

again Visakh :) thanks a lot
the query you gave, works great in MS SQL but I don't know why reporting service doesn't recognize partition!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:25:41

quote:
Originally posted by Peace2007

again Visakh :) thanks a lot
the query you gave, works great in MS SQL but I don't know why reporting service doesn't recognize partition!

so what you want is reporting service expression?

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-07 : 07:41:16

No not an expression I'm writing the query for the report dataset so it shouldn't make any difference
It seems there's something wrong with my MS SQL server I'll come back to you in a while

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:50:10

quote:
Originally posted by Peace2007

No not an expression I'm writing the query for the report dataset so it shouldn't make any difference
It seems there's something wrong with my MS SQL server I'll come back to you in a while

make sure you're using this on sql server 2005 db with compatibility level 90 else it wont recognise row_number() function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:53:43

quote:
Originally posted by Peace2007

it works fine in reporting service as well

Thanks a million :)

welcome as always

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-07 : 08:03:10

how I should know that compatibility level is 90?!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 08:04:33

quote:
Originally posted by Peace2007

how I should know that compatibility level is 90?!

execute below

Exec sp_dbcmptlevel 'yourdbname'

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-07 : 08:08:53

actually it worked once and not more! :( I put the query in a view and am given following error:
The OVER SQL construct or statement is not supported.

I'm using this on sql server 2005 db with compatibility level 90

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 08:14:23

quote:
Originally posted by Peace2007

actually it worked once and not more! :( I put the query in a view and am given following error:
The OVER SQL construct or statement is not supported.

I'm using this on sql server 2005 db with compatibility level 90

show the view code

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-07 : 08:16:49

[code]SELECT vw_UserInformation.User_Name, vw_UserInformation.Employee_Code, vw_UserInformation.DepartmentCode, tb_MembersOfProject.ProjectID,
count(tb_MembersOfProject.ProjectID) OVER (partition BY vw_UserInformation.User_Name) AS prjCount
FROM tb_MembersOfProject INNER JOIN
vw_UserInformation ON tb_MembersOfProject.UserID = vw_UserInformation.Employee_Code
WHERE (vw_UserInformation.DepartmentCode = 'SWE')[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 08:19:58

quote:

Originally posted by Peace2007

SELECT     vw_UserInformation.[User_Name], vw_UserInformation.Employee_Code, vw_UserInformation.DepartmentCode, tb_MembersOfProject.ProjectID, 
                      count(tb_MembersOfProject.ProjectID) OVER (partition BY vw_UserInformation.[User_Name]) AS prjCount
FROM         tb_MembersOfProject INNER JOIN
                      vw_UserInformation ON tb_MembersOfProject.UserID = vw_UserInformation.Employee_Code
WHERE     (vw_UserInformation.DepartmentCode = 'SWE')

seems like User_name is function. try like above and see.

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-07 : 08:50:51

User_name is a column name however I put the brackets but the same error still exists!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 10:09:04

the error suggests as if you're running query on sql 2000 or lower db.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources