October 9, 2012 at 5:24 pm
Hi all,
I want to divide the same column, but with the first SELECT(numerator) with a specific condition, and the 2nd Select(Denomator) as the total. Is that achievable? Below is the code I have.
(SELECT sum(Reviewed) + sum(Non Reviewed) AS Total
FROM tbl a
where IBEGINTIME >= '01-Aug-2012' AND
IBEGINTIME < '01-sep-2012'
AND Time <= 20)
/ 'Divide
(select
sum(Reviewed) + sum(Non Reviewed) As Total
from tbl a
where IBEGINTIME >= '01-Aug-2012' AND
IBEGINTIME < '01-sep-2012')
October 9, 2012 at 5:40 pm
Hey Alan,
I'm not entirely sure what you're trying to do here. I keep thinking I get the gist then realize I don't.
If you take a look at the first link in my signature, it'll show you what we usually prefer for example setups so we can get you example code that's tested and we're sure works. Basically: immediately consumable Schema, sample data, expected resultset.
Your code looks like it just wants to total unique sum conditions, but I'm not sure how the division comes into play from what you've provided so far.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 9, 2012 at 6:44 pm
Not 100% sure Alan, but I think I get it. Two ways (if I'm right).
DECLARE @Sums TABLE
(ID INT IDENTITY, Reviewed FLOAT, [Non Reviewed] FLOAT)
INSERT INTO @Sums
SELECT 251, 324 UNION ALL SELECT 124, 432 UNION ALL SELECT 532, 233
SELECT Reviewed, [Non Reviewed]
,Division=(Reviewed + [Non Reviewed]) /
(SUM(Reviewed + [Non Reviewed]) OVER (PARTITION BY (SELECT NULL)))
FROM @Sums
;WITH Total AS (
SELECT Total=SUM(Reviewed + [Non Reviewed])
FROM @Sums)
SELECT Reviewed, [Non Reviewed]
,Division=(Reviewed + [Non Reviewed]) / Total
FROM @Sums
CROSS APPLY Total
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply