Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2014
    Posts
    35

    Unanswered: Trying to find the difference between two sub-totals (sales - credits)

    Hi Everyone,

    I have the following code which essentially lists the total sales for an items on the first row, and the total credits for the same item on the second row.

    Code:
    SELECT T0.ItemCode, SUM(T0.LineTotal) as 'Total Sales'
    FROM INV1 T0
    WHERE T0.ItemCode = 'ACR2401010'
    GROUP BY T0.ItemCode
    
    UNION ALL
    
    SELECT T1.ItemCode, SUM(T1.LineTotal) as 'Total Sales'
    FROM RIN1 T1
    WHERE T1.ItemCode = 'ACR2401010'
    GROUP BY T1.ItemCode
    The results of the query are shown below (with some alterations for confidentiality).

    Query Result (image)

    What I would like to do is write a code block that subtracts the total credits from the total sales, leaving me with only one row of data for the ItemCode.

    If anybody can help with writing the code to achieve this it will be greatly appreciated.

    Kind Regards,

    Davo

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are there more than 1 row for an ItemCode(e.g. 'ACR2401010') in INV1 and RIN1?

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Do you mean something like:
    Code:
    select ItemCode, sum('Total Sales'), sum('Total Credits')
      ,  sum('Total Sales') - sum('Total Credits')
       from (
    SELECT T0.ItemCode, SUM(T0.LineTotal) as 'Total Sales', 0 as 'Total Credits'
    FROM INV1 T0
    WHERE T0.ItemCode = 'ACR2401010'
    GROUP BY T0.ItemCode
    
    UNION ALL
    
    SELECT T1.ItemCode, 0 as 'Total Sales', SUM(T1.LineTotal) as 'Total Credits'
    
    FROM RIN1 T1
    WHERE T1.ItemCode = 'ACR2401010'
    GROUP BY T1.ItemCode)
    GROUP BY ItemCode
    Dave

  4. #4
    Join Date
    Mar 2014
    Posts
    35
    Hello Dave,

    Thankyou for your kind response, and for taking the time to help.

    Kind Regards,

    David

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This might be another solution.

    Note: If there was only 1 row for an ItemCode(e.g. 'ACR2401010') in INV1 or RIN1,
    then the subquery corresponding to the table might be unneccesary (Just join the table itself).
    Code:
    SELECT T0.ItemCode
         , 'Total Sales' , 'Total Credits'
         , 'Total Sales' - 'Total Credits' as Difference
     FROM  (SELECT ItemCode, SUM(LineTotal) as 'Total Sales'
             FROM  INV1
             WHERE ItemCode = 'ACR2401010'
             GROUP BY
                   ItemCode
           ) as T0
     INNER JOIN
           (SELECT ItemCode, SUM(LineTotal) as 'Total Credits'
             FROM  RIN1
             WHERE ItemCode = 'ACR2401010'
             GROUP BY
                   ItemCode
           ) as T1
      ON   T1.ItemCode = T0.ItemCode
    ;

  6. #6
    Join Date
    Mar 2014
    Posts
    35
    Hi tonkuma,

    Thanks for adding your response to my post. I have ultimately used your style of coding. A quick question (if you don't mind), when creating an alias from a sub-query is it possible to include AS after the closing bracket? E.g.: ) AS T0, instead of just ) T0

    Kind Regards,

    Davo
    Last edited by Darts75; 05-26-14 at 21:03.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    when creating an alias from a sub-query is it possible to include AS after the closing bracket? E.g.: ) AS T0, instead of just ) T0
    Yes!
    Please see the Syntax and the Arguments in FROM (Transact-SQL)

    Syntax

    Code:
    [ FROM { <table_source> } [ ,...n ] ] 
    <table_source> ::= 
    {
        table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
            [ WITH ( < table_hint > [ [ , ]...n ] ) ] 
        | rowset_function [ [ AS ] table_alias ] 
            [ ( bulk_column_alias [ ,...n ] ) ] 
        | user_defined_function [ [ AS ] table_alias ] ]
        | OPENXML <openxml_clause> 
        | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
    ...
    ...
    ...
    }
    Arguments

    ...
    ...
    ...

    derived_table
    Is a subquery that retrieves rows from the database. derived_table is used as input to the outer query.

    derived _table can use the Transact-SQL table value constructor feature to specify multiple rows.
    For example, SELECT * FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);.
    For more information, see Table Value Constructor (Transact-SQL).

  8. #8
    Join Date
    Mar 2014
    Posts
    35
    Great, thank you. I will familiarize myself with the finer details of the FROM clauses syntax.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •