Divide 2 rows in same column

  • 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

  • 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 4 years, 6 months ago by  nigel..
    • This reply was modified 4 years, 6 months ago by  nigel.. Reason: fix typo
  • 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. 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

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

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