February 16, 2011 at 6:33 am
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.
February 16, 2011 at 6:42 am
How about this?
SELECT T1C1 , T2C1
FROM
( select SUM(Col1) T1C1 FROM T1 ) A
CROSS JOIN
( select SUM(Col1) T2C1 FROM T2 ) B
February 16, 2011 at 7:23 am
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.
February 16, 2011 at 8:12 am
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 🙂
February 16, 2011 at 1:11 pm
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.
February 16, 2011 at 6:50 pm
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
February 17, 2011 at 12:30 am
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 🙂
February 17, 2011 at 1:54 am
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 🙂
February 25, 2011 at 1:48 pm
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/
April 23, 2015 at 3:46 pm
Thank you! Thank you! i did learn something new 🙂
June 25, 2018 at 8:46 pm
ColdCoffee - Wednesday, February 16, 2011 6:42 AMthis fine I trying from long time but what about if I want to group by type id for 2 sum function please
June 26, 2018 at 7:19 am
maher_guida - Monday, June 25, 2018 8:46 PMColdCoffee - Wednesday, February 16, 2011 6:42 AMthis 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/
June 26, 2018 at 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 ?????
June 26, 2018 at 12:50 pm
maher_guida - Tuesday, June 26, 2018 12:25 PMhow 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
lookI 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 restgroup 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.
June 26, 2018 at 4:23 pm
Luis Cazares - Tuesday, June 26, 2018 12:50 PMmaher_guida - Tuesday, June 26, 2018 12:25 PMhow 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
lookI 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 restgroup 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 15 total)
You must be logged in to reply to this topic. Login to reply