Syntax Question - 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 2012 Forums

Transact-SQL (2012)

Syntax Question

Author

Topic

DatabaseStudent
Yak Posting Veteran

71 Posts

Posted - 2014-06-21 : 20:08:33

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY [ColumnResult] Asc)
OVER (PARTITION BY [GroupField]) AS [NewField]

I want to add WHERE FieldA > 1

Is there a way to write this to include a WHERE clause on the ColumnResult? So get the median by GroupField where FieldA criteria is greater than 1? Right now it is doing it for all records by Group rather than the subset by Group.

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-23 : 09:24:21

select count(FieldA) * 100.0 / (select count(*) from MyTable)
from MyTable
Where FieldA > 1
group by FieldName;

We are the creators of our own reality!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-23 : 12:25:21

The median, not the filtered average.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-23 : 13:03:52

quote:
Originally posted by DatabaseStudent

PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY [ColumnResult] Asc)
OVER (PARTITION BY [GroupField]) AS [NewField]

I want to add WHERE FieldA > 1

Is there a way to write this to include a WHERE clause on the ColumnResult? So get the median by GroupField where FieldA criteria is greater than 1? Right now it is doing it for all records by Group rather than the subset by Group.

why not first select the subset then compute the median? That is,set up a subquery to filter the rows WHERE FieldA > 1 and do the Percentile...etc on the filtered subquery?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-23 : 14:28:36

A median is based on an ordered set.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-23 : 14:41:48

Since OP already indicated he is on at least SQL Server 2012

DECLARE	@Sample TABLE
	(
		x INT NOT NULL
	)
INSERT	@Sample
	(
		x
	)
VALUES	(-3),
	(-3),
	(-1),
	( 0),
	( 1),
	( 2),
	( 2),
	( 3),
	( 4),
	( 5),
	( 6);

-- SwePeso
SELECT	AVG(1.0E * x) AS [Median]
FROM	(
		SELECT	x,
			2 * SUM(CASE WHEN x > 1 THEN 1 ELSE 0 END) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - SUM(CASE WHEN x > 1 THEN 1 ELSE 0 END) OVER () AS y
		FROM	@Sample
	) AS d
WHERE	y BETWEEN 0 AND 2;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources