SUM of 2 columns in 2 tables THIS IS REALLY SIMPLE

  • brett.y

    Ten Centuries

    Points: 1310

    I want to SUM a column in one table and SUM a column of another table and have the two SUM results appear in one table of two columns, one row, just as a query (not creat a new table). I tried UNION but this gives one column and two rows.

    If a do the following:

    select SUM(T1.kW),SUM(T2.kW)

    from T1,T2

    The results are massively wrong, they seem to multiply somewhere.

    If I have the two tables:

    T1. T2.

    kW kW

    1 10

    2 11

    3 12

    Then I want the result to be

    Kw kW

    6 33

    Both tables have column kW along with t_stamp. The ultimate intention is to query multiple tables (all basically recording kW with t_stamp) to get the SUM of each kW column from "start date/time" to "end date/time) into a table that is one row with x amount of columns (depending on amount of kW tables in query). This is for a SCADA package to use to draw a bar graph, i.e. eventually be able to get total kW (SUM) for a 4, 8 or 24 hour period of multiple tables using t_stamp.

    I would be happy to exclude the t_stamp for now just so I can get the SUM's.

  • ColdCoffee

    SSC-Dedicated

    Points: 39971

    How about this?

    SELECT T1C1 , T2C1

    FROM

    ( select SUM(Col1) T1C1 FROM T1 ) A

    CROSS JOIN

    ( select SUM(Col1) T2C1 FROM T2 ) B

  • brett.y

    Ten Centuries

    Points: 1310

    Thank you ColdCoffee that is EXACTLY what I was after. I just now have to work on the where clause. I have wasted 4 hours tring to achieve what took you 5 minutes.

  • ColdCoffee

    SSC-Dedicated

    Points: 39971

    brett.y (2/16/2011)


    Thank you ColdCoffee that is EXACTLY what I was after. I just now have to work on the where clause. I have wasted 4 hours tring to achieve what took you 5 minutes.

    :blush: Thanks for the feedback Brett. Nothing goes waste if u are gaining knowledge out of it 🙂

  • brett.y

    Ten Centuries

    Points: 1310

    I dont suppose anybody would be kind enough to explain why there is an A and a B at the end of both selects. If they are removed then this query does not work, but I have googled all the joins and I cannot find any examples that look like this.

  • ColdCoffee

    SSC-Dedicated

    Points: 39971

    brett.y (2/16/2011)


    I dont suppose anybody would be kind enough to explain why there is an A and a B at the end of both selects. If they are removed then this query does not work, but I have googled all the joins and I cannot find any examples that look like this.

    A and B are called Table Aliases . To learn more about them , click here

  • brett.y

    Ten Centuries

    Points: 1310

    Thanks again ColdCoffee really appreciate it. I would say they are alias for the two "virtual" tables created for the first select to pick from? I have read about alias in SQL for Dummies but when used in their examples (and the ones I have tried myself) the alias appears in the SELECT, then declared in the FROM and then have to be used also in the WHERE........ yet these seem to be declared but then never used???. But at least I understand now (kind of) lol. Thanks 🙂

  • ColdCoffee

    SSC-Dedicated

    Points: 39971

    They could be used in SELECT when there is a column from the 2 tables having the same name. Like

    SELECT A.Sum_Col1 , B.Sum_Col1

    FROM

    ( select SUM(Col1) Sum_Col1 FROM T1 ) A

    CROSS JOIN

    ( select SUM(Col1) Sum_Col1 FROM T2 ) B

    Else, if all the column names in both the tables are distinct, u need not explicitly address them with Aliases. Hope this helps 🙂

  • Sean Lange

    SSC Guru

    Points: 286402

    The other thing you should learn from this is why your original results were wrong. When you joined your tables with no condition you were actually creating the sum of the cartesian. This is why ColdCoffee used the subselect the way he did so that you got the total of each table and not the really high numbers you saw at first.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • livia.constantinescu

    SSC Journeyman

    Points: 95

    Thank you! Thank you! i did learn something new 🙂

  • maher_guida

    Valued Member

    Points: 61

    ColdCoffee - Wednesday, February 16, 2011 6:42 AM

    this fine I trying from long time but what about if I want to group by type id for 2 sum function please 

  • Sean Lange

    SSC Guru

    Points: 286402

    maher_guida - Monday, June 25, 2018 8:46 PM

    ColdCoffee - Wednesday, February 16, 2011 6:42 AM

    this fine I trying from long time but what about if I want to group by type id for 2 sum function please 

    It would probably be best if you create a new question and provide some specifics of what you are trying to accomplish. See the first link in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • maher_guida

    Valued Member

    Points: 61

    how ican make 3 sum function in same select query from 3tables
    and there is  relation the problem is sum come wrong numbers and  one of the sum come null I want it 0  to complet calculations
    look 

    I have three tables
    types
    bills_in
    bills_out
    iwant sum(bills_in.[quantity]),sum(bills_out.[quantity]),(bills_in.[quantity])-sum(bills_out.[quantity]) as rest

    group by types.name
    and sometimes su(bills_n.[quantity]) null  then will give me the result of (bills_in.[quantity])-sum(bills_out.[quantity]) as rest null
    I am using oledb/ace12
    what I can do ?????

  • Luis Cazares

    SSC Guru

    Points: 183524

    maher_guida - Tuesday, June 26, 2018 12:25 PM

    how ican make 3 sum function in same select query from 3tables
    and there is  relation the problem is sum come wrong numbers and  one of the sum come null I want it 0  to complet calculations
    look 

    I have three tables
    types
    bills_in
    bills_out
    iwant sum(bills_in.[quantity]),sum(bills_out.[quantity]),(bills_in.[quantity])-sum(bills_out.[quantity]) as rest

    group by types.name
    and sometimes su(bills_n.[quantity]) null  then will give me the result of (bills_in.[quantity])-sum(bills_out.[quantity]) as rest null
    I am using oledb/ace12
    what I can do ?????

    Start by creating a new thread and read the articles on my signature to learn how to get better help.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • maher_guida

    Valued Member

    Points: 61

    Luis Cazares - Tuesday, June 26, 2018 12:50 PM

    maher_guida - Tuesday, June 26, 2018 12:25 PM

    how ican make 3 sum function in same select query from 3tables
    and there is  relation the problem is sum come wrong numbers and  one of the sum come null I want it 0  to complet calculations
    look 

    I have three tables
    types
    bills_in
    bills_out
    iwant sum(bills_in.[quantity]),sum(bills_out.[quantity]),(bills_in.[quantity])-sum(bills_out.[quantity]) as rest

    group by types.name
    and sometimes su(bills_n.[quantity]) null  then will give me the result of (bills_in.[quantity])-sum(bills_out.[quantity]) as rest null
    I am using oledb/ace12
    what I can do ?????

    Start by creating a new thread and read the articles on my signature to learn how to get better help.

    what do u mean I cant understand u

Viewing 15 posts - 1 through 15 (of 16 total)

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