cross apply and group by - 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 2008 Forums

Transact-SQL (2008)

cross apply and group by

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-08-17 : 07:16:11

Hi,


select sum(cnt) from table1 t1
cross apply
 (select COUNT(t2.pid) cnt, t1.mainid  from table1 t2 where t2.mainid = t1.mainid) d
group by pid

I have above query which uses group on one column and returns with appropriate count, but if i add extra column it breaks my group.

Is there any good solution or change using cross apply so that we can do group and also use other columns in list as well.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-17 : 07:29:16

In most cases, you will need to have any column that is outside of an aggregate function to be in a group by clause. So if the other columns that you want to get also can be aggregated (for example, max(anotherColumn)), that would work.

Alternatively, in SQL 2005 and above you can use the OVER() clause with aggregate functions - and you would not need to do group by. See a discussion and some examples here.

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-08-17 : 07:37:58

Thanks.

But I was searching with the way to group using cross apply to make separate query and use it. Any example you have using cross apply?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-17 : 07:46:56

you would be better off explaining what you want giving some data example without which its difficult for us to understand what exactly you're trying to achieve.
your current statement doesnot make much sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-08-17 : 07:51:07

Hi Visakh,

I get my output using OVER query but before that I was trying to achieve group by with other table columns using cross apply so I thought to ask you how to do using cross apply thats it.

Sorry if anything does not make much sense to you.
Thanks Visakh. : )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-17 : 07:55:10

quote:
Originally posted by keyursoni85

Hi Visakh,

I get my output using OVER query but before that I was trying to achieve group by with other table columns using cross apply so I thought to ask you how to do using cross apply thats it.

Sorry if anything does not make much sense to you.
Thanks Visakh. : )

thats fine but can you let us know what output you're getting out of which data so that we can provide query based on apply for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

keyursoni85
Posting Yak Master

233 Posts

Posted - 2011-08-17 : 08:59:30

Hi Visakh,

Its done now.

Thanks to you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources