Author |
Topic |
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-26 : 05:48:06
|
Hi there, I have two queries: SELECT SUM (BALANCE)FROM table_S AS SINNER JOIN table_F AS FON F.ID = S.ID WHERE CODE = 'OK'And the other one: SELECT SUM(BALANCE) FROM Table_SWHERE CODE = 'OK' Im supposed to have the same extraction as table_S contains all the info of Table_F... In the second query Im asking for the balance in Table_S, in the first query im supposed to have the same balance as the second query. Im just connecting the tables by Id...Any tip? Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 05:59:54
|
sorry didnt understand what issue is. As per your explanation provided relationship between tables is 1 to 1 you should get same result. Isnt it not observed output?Also check if relationship is one to one between them.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-04-26 : 06:01:01
|
Any question? INNER JOIN will sort out rows that have no match via ID in the joined table.So I guess that's the reason why you get different results... Too old to Rock'n'Roll too young to die. |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-26 : 06:04:50
|
if I use left outer join still not working.. The matter is that in table_f i have a column that I need for table_s and the only way to connect the data is via ID. The matter is that I cant have more ID's than the ones in table_s, But using inner join and left outer join i have much more ID's. And i don't know why |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-26 : 06:07:52
|
The relationship:Table_s is provides me with the information of some Id's from table_f. Table_f is the master table and table_s is a secondary table that gives me some information but I need to group by table_s using a column from table_f. that's why im trying to cross the dataany idea? Thank youquote: Originally posted by visakh16 sorry didnt understand what issue is. As per your explanation provided relationship between tables is 1 to 1 you should get same result. Isnt it not observed output?Also check if relationship is one to one between them.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-26 : 06:10:14
|
Did you display other columns of table_F along with SUM ?If not,SELECT SUM (BALANCE)FROM table_S AS SWHERE ID IN (SELECT ID FROM table_F )AND CODE = 'OK'--Chandu |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-26 : 06:23:35
|
Yes I did. Im going back to basis as the results are not matching. I found that the information extracted by crossing the data (without adding more columns), is not matching. SELECT SUM (BALANCE)FROM table_S AS SWHERE ID IN (SELECT ID FROM table_F ) >>> please could you be more specific with this part of the query?AND CODE = 'OK'Thank youquote: Originally posted by bandi Did you display other columns of table_F along with SUM ?If not,SELECT SUM (BALANCE)FROM table_S AS SWHERE ID IN (SELECT ID FROM table_F )AND CODE = 'OK'--Chandu
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 06:25:06
|
quote: Originally posted by jfm The relationship:Table_s is provides me with the information of some Id's from table_f. Table_f is the master table and table_s is a secondary table that gives me some information but I need to group by table_s using a column from table_f. that's why im trying to cross the dataany idea? Thank youquote: Originally posted by visakh16 sorry didnt understand what issue is. As per your explanation provided relationship between tables is 1 to 1 you should get same result. Isnt it not observed output?Also check if relationship is one to one between them.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
use derived tables likeSELECT f.*,TotalFROM table_F fINNER JOIN (SELECT ID, SUM(BALANCE) AS Total FROM table_S GROUP BY ID )sOn f.ID = s.ID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-26 : 06:34:11
|
quote: Originally posted by jfm Yes I did. Im going back to basis as the results are not matching. I found that the information extracted by crossing the data (without adding more columns), is not matching. SELECT SUM (BALANCE)FROM table_S AS SWHERE ID IN (SELECT ID FROM table_F ) >>> please could you be more specific with this part of the query? -- this is for checking existence of ID's in table_F AND CODE = 'OK'Thank youquote: Originally posted by bandi Did you display other columns of table_F along with SUM ?If not,SELECT SUM (BALANCE)FROM table_S AS SWHERE ID IN (SELECT ID FROM table_F )AND CODE = 'OK'-- Chandu
show us sample data and output you want out of it--Chandu |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-26 : 06:50:46
|
select count (ID) from table_s this is the secondary table 1,000select count (ID) from table_f this is the master table 10,000select count (ID) from table_s as S inner join file_f as Fon S.ID = F.ID8,000in the last query we can see that is incorrect the extraction as they have to be a total of 1,000 (the total file_s in file_f)So I need to cross the data and have 1,000 as an output. If that's is Ok, I will add the columns that I need in the query after that. Any tip? Thank youquote: Originally posted by bandi
quote: Originally posted by jfm Yes I did. Im going back to basis as the results are not matching. I found that the information extracted by crossing the data (without adding more columns), is not matching. SELECT SUM (BALANCE)FROM table_S AS SWHERE ID IN (SELECT ID FROM table_F ) >>> please could you be more specific with this part of the query? -- this is for checking existence of ID's in table_F AND CODE = 'OK'Thank youquote: Originally posted by bandi Did you display other columns of table_F along with SUM ?If not,SELECT SUM (BALANCE)FROM table_S AS SWHERE ID IN (SELECT ID FROM table_F )AND CODE = 'OK'-- Chandu
show us sample data and output you want out of it--Chandu
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 06:52:46
|
what does this return?select count (distinct S.ID) from table_s as S left join file_f as Fon S.ID = F.ID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-26 : 07:04:11
|
Ok thanks for distinct I was missing that part. So now this query i giving to me the same extraction as the total file_sLet me work on my big query and I will get back to you. Thanks a lotquote: Originally posted by visakh16 what does this return?select count (distinct S.ID) from table_s as S left join file_f as Fon S.ID = F.ID ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-26 : 07:25:30
|
ok fineyou're welcomelet us know if you need any more help------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jfm
Posting Yak Master
145 Posts |
Posted - 2013-04-26 : 11:46:21
|
SELECT F.I_date, F.N_Date, S.S_Date, S.Number/100 FROM Table_S AS S LEFT JOIN Table_F AS F ON S.ID = F.ID?? SUM(CASE WHEN S_Date-F.N_Date <'1' THEN S_Number/100 END) as '0' , SUM(CASE WHEN S_Date-F.N_Date ='1' THEN S_Number/100 END) as '1' , SUM(CASE WHEN S_Date-F.N_Date ='2' THEN S_Number/100 END) as '2' , SUM(CASE WHEN S_Date-F.N_Date ='3' THEN S_Number/100 END) as '3' , WHERE S.Ref = 'ok' GROUP BY F.I_date ORDER BY F.I_date What I need is this extraction. By using the query we have talked about and with the SUM(CASE ... The first problem was extract the balance in Table_S (secondary table), by connecting the tables using the only link: Id_Col. But to extract my data, I need to see the difference between dates_columns and extract a matrix from the SQL as is written: SUM(CASE ... Im very close to the solution, but im missing something. Can you help me in this final step? Thank youquote: Originally posted by visakh16 ok fineyou're welcomelet us know if you need any more help------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-29 : 00:28:54
|
something likeSELECT F.I_date, SUM(CASE WHEN DATEDIFF(dd,S_Date,COALESCE(F.N_Date,S_Date)) < 1 THEN S_Number/100 END) as [0] ,SUM(CASE WHEN DATEDIFF(dd,S_Date,COALESCE(F.N_Date,S_Date)) = 1 THEN S_Number/100 END) as [1] ,SUM(CASE WHEN DATEDIFF(dd,S_Date,COALESCE(F.N_Date,S_Date)) = 2 THEN S_Number/100 END) as [2] ,SUM(CASE WHEN DATEDIFF(dd,S_Date,COALESCE(F.N_Date,S_Date)) = 3 THEN S_Number/100 END) as [3] FROM Table_S AS S LEFT JOIN Table_F AS F ON S.ID = F.IDWHERE S.Ref = 'ok' GROUP BY F.I_date ORDER BY F.I_date ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|