Divide 2 rows in same column

Question

Post reply

Divide 2 rows in same column

girl_bj

SSCommitted

Points: 1731
More actions
September 6, 2019 at 6:17 am

#3677848

Hi, How can i divide 2 rows with condition.
Type | Year | Month | Car | Amount
A | 2019 | JAN | HONDA | 100
B | 2019 | JAN | HONDA | -90
C | 2019 | JAN | HONDA | -0.09
I want to count if Type = C then total amount B / A

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

nigel. SSChampion Points: 11636 More actions · Answer 1

Hi,

You will need to you provide some further information before anyone can help you with your problem.

Include at least the following:

i. DDL For the tables in question.

ii. Readily consumable data in the form of INSERT statements that can be run in SSMS

iii.The expected output

See the link below about posting questions to the forum for more details

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

This reply was modified 5 years, 2 months ago by nigel..
This reply was modified 5 years, 2 months ago by nigel.. Reason: fix typo

Nigel
How to post questions

nigel. SSChampion Points: 11636 More actions · Answer 2

Without the additional info we can only guess at your full reqirements.

My first 'guess' is as follows

CREATE TABLE #T (Type CHAR(1),Year INT, MOnth CHAR(3), Car VARCHAR(50), Amount decimal(18,2))


INSERT #T
SELECT 'A',2019,'JAN','HONDA',100.0
UNION ALL
SELECT 'B',2019,'JAN','HONDA',90.0
UNION ALL
SELECT 'C',2019,'JAN','HONDA',0.09

SELECT T1.*, Total = T3.Amount/T2.Amount
FROM #T T1
JOIN #T T2 ON T1.Year = T2.Year AND T1.Month=T2.Month and T1.Car = T2.CAR AND T2.Type='A'
JOIN #T T3 ON T1.Year = T3.Year AND T1.Month=T3.Month and T1.Car = T3.CAR AND T3.Type = 'B'
WHERE T1.Type = 'C'

Nigel
How to post questions

drew.allen SSC Guru Points: 76985 More actions · Answer 3

nigel. wrote:

Without the additional info we can only guess at your full reqirements.

My first 'guess' is as follows

CREATE TABLE #T (Type CHAR(1),Year INT, MOnth CHAR(3), Car VARCHAR(50), Amount decimal(18,2))


INSERT #T
SELECT 'A',2019,'JAN','HONDA',100.0
UNION ALL
SELECT 'B',2019,'JAN','HONDA',90.0
UNION ALL
SELECT 'C',2019,'JAN','HONDA',0.09

SELECT T1.*, Total = T3.Amount/T2.Amount
FROM #T T1
JOIN #T T2 ON T1.Year = T2.Year AND T1.Month=T2.Month and T1.Car = T2.CAR AND T2.Type='A'
JOIN #T T3 ON T1.Year = T3.Year AND T1.Month=T3.Month and T1.Car = T3.CAR AND T3.Type = 'B'
WHERE T1.Type = 'C'

This requires three scans of the table. You only need to use one.

SELECT
t.Type
,t.Year
,t.MOnth
,t.Car
,t.Amount
,CASE
WHEN t.Type = 'C'
THEN
SUM(CASE WHEN Type = 'B' THEN t.Amount ELSE 0 END) OVER(PARTITION BY t.Year, t.MOnth, t.Car)
/ NULLIF(SUM(CASE WHEN Type = 'A' THEN t.Amount END) OVER(PARTITION BY t.Year, t.MOnth, t.Car), 0)
END
FROM #T AS t

Also, you should avoid using reserved words for your field names. TYPE, YEAR, and MONTH are all reserved words.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA