Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Dividing Rows using conditions

Posted on 2014-01-23
Medium Priority
406 Views
Hi,
I am using MS SQL Server 2012. I want to use this query in SSRS.
I have the following table:
Year     Month   AccNo    Balance
2011     2           45         900
2011     3           45         700
2011     4          45          800
2011     1          50           20
2011     2          50           15
2011     3          50           40

I want to the following Division:
Balance of AccNo 45 / Balance of AccNo50
Where AccNo45.Year = AccNo50.Year
AND   AccNo45.Month = AccNo50.Month

The Answer should be something like this:
Year     Month     Ratio
2011     2            900/15 (=60)
2011     3            800/40 (=20)

Thanks.
0
Question by:fireblues
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 25

Accepted Solution

chaau earned 1400 total points
ID: 39805591
You need to self-join the same table:
``````SELECT a.Year, a.Month,  a.Balance/b.Balance AS Ratio
FROM AccountBalance a INNER JOIN AccountBalance b
ON a.Year = b.Year AND a.Month = b.Month
AND a.AccNo = 45 and b.AccNo = 50
``````
0

LVL 13

Expert Comment

ID: 39805594
ARe 45 and 50 the only 2 accounts you're interested in? I assume this is only a small set of the total data? If there are more accounts, how do you identify which account should be divided by which? If you want to standardize this in a query you need to know why you need to divide the 45 balance by the 50 balance and not the other way around.
0

Author Comment

ID: 39805605
@Chaau - I'll check the result and will let you know.
Yes, at the moment I am interested in division of only two values. Actually AccNo 50 is Quantity and AccNo45 is Sales. So I want to know the Ratio or say price.
0

LVL 25

Expert Comment

ID: 39805692
Just wanted to add: add " and b.Balance > 0" at the end of my statement to protect against a potential division by zero situation.
0

LVL 32

Expert Comment

ID: 39806877
>>The Answer should be something like this:
Year     Month     Ratio
2011     2            900/15 (=60)
2011     3            800/40 (=20)<<
Why is month 3 800/40 and not 700/40?
0

Author Comment

ID: 39807259
Yup, it would be 700/40 instead of 800/40.
Thanks Everyone :)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
###### Suggested Courses
Course of the Month11 days, 23 hours left to enroll