Is there a way to filter the parameter(s) within OVER(PARTITION BY ....) ? ? ?

Question

Post reply

Is there a way to filter the parameter(s) within OVER(PARTITION BY ....) ? ? ?

SuperJB

SSChasing Mays

Points: 628
More actions
October 25, 2010 at 3:29 am

#225434

Example:
Select
COUNT(col1) OVER(Partition By col1) as 'First' ,
COUNT(col1) OVER(Partition By col1) as 'Second' ,
COUNT(col1) OVER(Partition By col1) as 'Third',
...
...
...
From SomeTable
I'm trying to execute a query of this sortm but, i need to filter my "col1" for each column i want to return. Is there a way to do this ?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Does this do the trick?

DECLARE @tb TABLE (

col1 int

)

INSERT INTO @tb SELECT 1

INSERT INTO @tb SELECT 2

INSERT INTO @tb SELECT 3

INSERT INTO @tb SELECT 4

INSERT INTO @tb SELECT 5

INSERT INTO @tb SELECT 6

SELECT

COUNT(CASE WHEN col1 = 1 THEN 1 END) OVER(Partition By col1) as 'First' ,

COUNT(CASE WHEN col1 = 2 THEN 1 END) OVER(Partition By col1) as 'Second' ,

COUNT(CASE WHEN col1 = 3 THEN 1 END) OVER(Partition By col1) as 'Third'

FROM @tb

-- Gianluca Sartori

iBar SSCertifiable Points: 5872 More actions · Answer 2

Based on your example, Not really sure what you really want.

Try "Ntile" in the in-line view and then write a grouping query on that result.

Otherwise give some more specific and clear requirement please.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

john.acb (10/25/2010)
Example:
Select
COUNT(col1) OVER(Partition By col1) as 'First' ,
COUNT(col1) OVER(Partition By col1) as 'Second' ,
COUNT(col1) OVER(Partition By col1) as 'Third',
...
...
...
From SomeTable
I'm trying to execute a query of this sortm but, i need to filter my "col1" for each column i want to return. Is there a way to do this ?

John, would you have time to take Gianluca's sample data script and expand on it to provide a table more representative of the one you are using as your source? It would also be helpful to include what you expect as output from the same sample data.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SuperJB SSChasing Mays Points: 628 More actions · Answer 4

SuperJB

SSChasing Mays

Points: 628

October 25, 2010 at 4:41 am

#1241479

Gonna try out our code... check back soon

Thanks

SuperJB SSChasing Mays Points: 628 More actions · Answer 5

Hall of Fame, my example would be something like this:

SELECT

col1,

col2,

col3,

...

COUNT(CASE WHEN PersonsAge BETWEEN 0 AND 3 THEN PersonsAge END) OVER(Partition By col1) as 'Baby' ,

COUNT(CASE WHEN PersonsAge BETWEEN 4 AND 12 THEN PersonsAge END) OVER(Partition By col1) as 'Child' ,

COUNT(CASE WHEN PersonsAge BETWEEN 13 AND 18THEN PersonsAge END) OVER(Partition By col1) as 'Teen'

FROM PersonData

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

john.acb (10/25/2010)
Hall of Fame, my example would be something like this:
SELECT
col1,
col2,
col3,
...
...
COUNT(CASE WHEN PersonsAge BETWEEN 0 AND 3 THEN PersonsAge END) OVER(Partition By col1) as 'Baby' ,
COUNT(CASE WHEN PersonsAge BETWEEN 4 AND 12 THEN PersonsAge END) OVER(Partition By col1) as 'Child' ,
COUNT(CASE WHEN PersonsAge BETWEEN 13 AND 18THEN PersonsAge END) OVER(Partition By col1) as 'Teen'
FROM PersonData

Without a sample of your data and a picture of what you expect the output to look like, anything offered here will be pretty much a stab in the dark. Here's another stab in the dark to join the one offered by Gianluca:

SELECT

col1,

col2,

col3,

...

SUM(CASE WHEN PersonsAge BETWEEN 0 AND 3 THEN 1 ELSE 0 END) OVER(Partition By col1) as 'Baby' ,

SUM(CASE WHEN PersonsAge BETWEEN 4 AND 12 THEN 1 ELSE 0 END) OVER(Partition By col1) as 'Child' ,

SUM(CASE WHEN PersonsAge BETWEEN 13 AND 18 THEN 1 ELSE 0 END) OVER(Partition By col1) as 'Teen'

FROM PersonData

Is there a good reason why you are avoiding GROUP BY?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SuperJB SSChasing Mays Points: 628 More actions · Answer 7

Gianluca Sartori,

Thanks, your solution helped me finally figure out how to solve the problem! I didn't use the method exactly as you explained, but i based my solution on yours...

Thanks a lot,

SuperJB 😎

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

spaghettidba

SSC Guru

Points: 105732

October 25, 2010 at 8:55 am

#1241642

You're welcome.

Glad I could help

-- Gianluca Sartori