none
Working on a SELECT Output

    Question

  • Hi

    Please can anyone help me.

    I've just joined two table together. Next I would like to output the SUM of one of the columns.

    I am almost a complete beginner so please could anyone give me some pointers on how to do this.

    Thank you

    Matthew

    Sunday, September 05, 2010 8:21 PM

Answers

  • If you need to show sum of one column, it means you want to group by all other columns.

    Example:

     

    select SOD.SalesOrderID, C.CustomerType, 
    sum(SOD.OrderQty * SOD.UnitPrice) as OrderTotal 
    from AdventureWorks.Sales.SalesOrderHeader OH
    inner join 
    AdventureWorks.Sales.SalesOrderDetail SOD 
    on OH.SalesOrderID = SOD.SalesOrderID inner join AdventureWorks.Sales.Customer C
    on OH.CustomerID = C.CustomerID 
    group by SOD.SalesOrderID, C.CustomerType
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Atif-ullah Sheikh Monday, September 06, 2010 3:24 AM
    • Marked as answer by Ai-hua Qiu Monday, September 13, 2010 7:33 AM
    Monday, September 06, 2010 12:29 AM
    Moderator
  • Others have already provided sample code to illustrate how SUM works. But to
    receive the best answer is it recommended that you provide your tables structure
    (CREATE TABLE statements) and INSERT statements for some sample data, then the
    desired result set. This will avoid any guessing. Also, if the join between the
    tables can produce multiple matches then you may need to pre-aggregate one of
    the tables (using derived table or CTE) to avoid incorrect summing results.
     
    If you are interested to learn T-SQL, a great book is T-SQL Fundamentals:
     

    Plamen Ratchev
    • Proposed as answer by Atif-ullah Sheikh Monday, September 06, 2010 3:25 AM
    • Marked as answer by Ai-hua Qiu Monday, September 13, 2010 7:33 AM
    Monday, September 06, 2010 2:32 AM
    Moderator

All replies

  • select sum(table1.column1)     table1sum
            ,tabl11.column2
    from schema.table1   table1
    join schema.table2 table2   on table1.key = table2.key
    group by table11.column2

    You need to provide much more information for me to be more helpful.

     

     


    Tom
    Sunday, September 05, 2010 8:36 PM
  • If you need to show sum of one column, it means you want to group by all other columns.

    Example:

     

    select SOD.SalesOrderID, C.CustomerType, 
    sum(SOD.OrderQty * SOD.UnitPrice) as OrderTotal 
    from AdventureWorks.Sales.SalesOrderHeader OH
    inner join 
    AdventureWorks.Sales.SalesOrderDetail SOD 
    on OH.SalesOrderID = SOD.SalesOrderID inner join AdventureWorks.Sales.Customer C
    on OH.CustomerID = C.CustomerID 
    group by SOD.SalesOrderID, C.CustomerType
    

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Proposed as answer by Atif-ullah Sheikh Monday, September 06, 2010 3:24 AM
    • Marked as answer by Ai-hua Qiu Monday, September 13, 2010 7:33 AM
    Monday, September 06, 2010 12:29 AM
    Moderator
  • Others have already provided sample code to illustrate how SUM works. But to
    receive the best answer is it recommended that you provide your tables structure
    (CREATE TABLE statements) and INSERT statements for some sample data, then the
    desired result set. This will avoid any guessing. Also, if the join between the
    tables can produce multiple matches then you may need to pre-aggregate one of
    the tables (using derived table or CTE) to avoid incorrect summing results.
     
    If you are interested to learn T-SQL, a great book is T-SQL Fundamentals:
     

    Plamen Ratchev
    • Proposed as answer by Atif-ullah Sheikh Monday, September 06, 2010 3:25 AM
    • Marked as answer by Ai-hua Qiu Monday, September 13, 2010 7:33 AM
    Monday, September 06, 2010 2:32 AM
    Moderator
  • Hi

    I definitely need to learn the GROUP command. The problem in my life is that I learn SQL and then go onto something else and have to re-learn it all again.

    Thanks for recommending the book T-SQL Fundamentals book. I had a look some of the contents of the book but I wasn't sure it looked a bit complicated - I'm not sure.

    Could anyone recommend any more SQL books please.

    Thanks

    Matthew

    Tuesday, September 07, 2010 7:51 PM