Creating "% out of total" colu,m
-
Hello.
I have Table A with columns B, C and D. Column B contain status ('Yes' or 'No'), C is a measure, and D is area ('X', 'Y' or 'Z').
What I want to do is to work out % of Yes out of total - (C where B=Yes)/(C where B=Yes)+(C where B=No), then to be grouped by D.
Sample source and the expected output should look like
B D C (C where B=Yes)/(C where B=Yes)+(C where B=No)
-------------------------------------------
Yes X 10 0.303030303
No X 23
Yes Y 188 0.497354497
No Y 190
Yes Z 65 0.792682927
No Z 17
I am still new at this so I don't have a clue how to achive this. Is there a simple SQL to create output like this?
Thanks for your help.
-
This should do it - uses a co-related subquery:
select t.b, t.d, t.c,
Calc = case t.b
when 'yes' then t.c / (select cast(sum(t2.c) as decimal(19)) from A t2 where t2.d = t.d)
else 0
end
from A t
It's not that simple - let me know if you'd like any additional explanation.
-
Hiya, your suggested method work in a way, but I can't get the actual output of the calculation right: using the same sourse data example I gave earlier, I expected
D Calculated by hand
----------------------
X 0.303
Z 0.49
Y 0.79
But actually get
D Calclated in SQL
----------------------
X 0.153
Z 0.417
Y 0.25
which are quite different from what it should be... have I gone wrong somewhere?
-
I actually typed in your data to test my query, so it should be right
Please post the actual SQL that is generating the wrong results for you and I will check for any typos.
-
Here's my suntax (based on your example)
select TAB1.B, TAB1.D,
Calc = SUM(TAB1.C) / (select cast(sum(TAB2.C) as decimal(19))
from TABLE2 TAB2
where TAB2.D = TAB1.D
AND TAB2.B IN ('YES','NO')
AND DATE BETWEEN '2005-10-01 00:00:00.000' AND '2005-10-31 23:59:00.000'
)
from TABLE1 TAB1
WHERE
AND TAB1.B ='YES'
AND DATE BETWEEN '2005-10-01 00:00:00.000' AND '2005-10-31 23:59:00.000'
GROUP BY TAB1.B, TAB1.D
Column B has more than just 'Yes' and 'No', but I'm only interested with data where it has either 'Yes' or 'No', hence the WHERE clause.
-
You appear to have ditched my CASE statement, for some reason! That's the way to get zero returned if B is 'no'.
Also, you should prefix your date column selections (tab1.date, tab2.date etc), to avoid possible ambiguity.
You do not need the GROUP BY - use ORDER BY instead.
You need to change your overall selection criteria
AND TAB1.B ='YES'
to
AND TAB1.B ='YES' or TAB1.B = 'NO'
-
Aha - I followed your syntax completly and I got the correct result this time! Thank you so much for your help, much appreciated.
Regards
Fiona
-
Any time, glad to help.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply