Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Windowed Aggregate functions Expand / Collapse
Author
Message
Posted Friday, June 27, 2008 10:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 11:08 AM
Points: 1,371, Visits: 385
I really hate marking the wrong anwser selection letter after determing the right output. My hand eye coordination wasn't that good back in the days of PE either.

-- Mark D Powell --
Post #525197
Posted Friday, June 27, 2008 11:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:07 PM
Points: 5,590, Visits: 24,976
thuybi

More than likely it is a typographical error, for example the following is missing the ) directly after the asterik

select grp,subgrp,
count(* OVER

Should be
select grp,subgrp,
count(*)OVER


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #525204
Posted Friday, June 27, 2008 11:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:28 AM
Points: 7,139, Visits: 15,190
thuybui (6/27/2008)
Yes,
Microsoft SQL Server Management Studio 9.00.1399.00


And what is the compatibility level of the DB in question?


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #525211
Posted Friday, June 27, 2008 11:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:53 PM
Points: 1,521, Visits: 3,039
Excellent QOD. I spent enough time studying the code and the results in QA that I don't feel guilty about running it before choosing the correct answer .

The light bulb finally went on for me that including "subgrp" in the result set is a red herring of sorts -- none of the three counts change depending on its value. The results are easier to understand by removing subgrp and making them distinct. If we make the Select statement....
select distinct grp --,subgrp
,count(*) OVER (partition by grp,subgrp) grpcount
,count(*) OVER (partition by grp) subcount
,count(*) OVER (partition by NULL) grandcount
from #windows
group by grp , subgrp


Then our results are much more straight-forward and, to me anyway, easier to tie back to the query:
grp   grpcount    subcount    grandcount
----- ----------- ----------- -----------
aaaaa 1 3 4
bbbbb 1 1 4

Putting the results in plain English,
there's 1 grp "aaaaa" with 3 subgrps out of 4 total subgrps,
there's 1 grp "bbbbb" with 1 subgrp out of the same total 4 subgrps

The grpcount will always be 1 since we did "group by" and windowed on the same combination of grp and subgrp.
Post #525231
Posted Friday, June 27, 2008 12:06 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, July 27, 2014 6:09 PM
Points: 41, Visits: 108
Thanks. I was able to run a quey in a DB with compatibility set to 90. The previous DB only has compatibility set up to 80.
Post #525247
Posted Monday, June 30, 2008 2:22 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:30 AM
Points: 5,344, Visits: 1,388
Excellent question....


Post #525767
Posted Monday, June 30, 2008 5:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2008 5:49 AM
Points: 1, Visits: 0
I too got the same error, Please review this case ...
Post #525821
Posted Thursday, February 26, 2009 8:18 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 15, 2010 4:02 PM
Points: 374, Visits: 266
I would have thought not having an ifexists() on the first line would ensure this would fail (table not existing to drop), though I second guessed the question and got it right.
Post #665465
Posted Friday, December 7, 2012 4:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
good one


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1393983
Posted Monday, August 25, 2014 3:55 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:37 PM
Points: 2,677, Visits: 215
Just got to count them up.
Post #1607274
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse