General TSQL question-
-
Help Please...
I have a four col result set from a union query.
id
desc
count
flag
1
book
4
total
1
book
8
Gtotal
2
notebook
6
total
2
notebook
8
Gtotal
I'd like to select id, desc and a third col (count of total/count of Gtotal) from this result set. Is there a way to do this without creating temp tables? All suggestions are appreciated.
Thanks.
-
Let's say you original SELECT was
SELECT id, desc, SUM(Total) as [count], SUM(GTotal) as flag
FROM MyTable
GROUP BY id, desc, flag
then you can use the original query as a derived table as follows...
SELECT id, desc,
SUM(CASE WHEN flag = 'Total' then [count] else 0 end) /
SUM(CASE WHEN flag = 'GTotal' then [count] else 0 end) as Expr
FROM (
SELECT id, desc, SUM(Total) as [count], SUM(GTotal) as flag
FROM MyTable
GROUP BY id, desc, flag) OrgSet
GROUP BY id, desc
Something like this should work OK, you may want to add ZERO checking in the devisor if it's a poss. value.
Once you understand the BITs, all the pieces come together
-
Thank you ThomasH. I'll try it first thing in the morning. Although I'm not very clear on sum(Total) and sum(GTotal) since they are texts and not number.
Thanks again,
-
select [id], [desc], convert(float,Sum_Total) / convert(float,Sum_GTotal)
from (
SELECT [id], [desc], SUM(case flag when "Total" then [count] else 0 end ) as Sum_Total, SUM(case flag when "GTotal" then [count] else 0 end ) as Sum_GTotal
FROM MyTable
GROUP BY [id], [desc]
) A
order by [id], [desc]
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank you for replying but unfortunately neither worked for me. I think that I'll make separate tables and deal with them that way.
-
Don't give up yet...
Can you post your SELECT that gives yo your original result set?
Once you understand the BITs, all the pieces come together -
the double quotes should be single quotes in this query. Then it should work just fine.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank you alzdba I'll give it a try, however, I was able to do what I wanted by reshaping the query from vertical to horizontal. Unfortunantly, I was not able to post yesterday as I was not able to type in this box nor the subject box
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply