Author |
Topic |
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-07-17 : 06:03:53
|
The data in my table is as followsName Boys GirlsSaurabh 50.20 30Santhi 80 7Satish 90 10.10Kumar 12 23Here three columns are varchar columns. But the columns Boys and Girls contains decimal values. Now my requirement is i will give the combination of names and it has find the sum of boys and girls. I need out put as follows if i give input as 'satish kumar' and 'shanthi saurabh'. Due to business requirement the boys and girls column are varchar type.Output Satish Kumar 102 33.10Santhi Saurabh 130.20 37I tried below query. But getting problem when making a sum(due to conversion problem i think.)Select sum(Convert(decimal,boy)) as B, sum(Convert(decimal,girl)) as G from TBL_genderwhere Name = 'ASPEN' or Name = 'ASSAD'G. Satish |
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-07-17 : 06:07:12
|
quote: combination of names
what do you mean that? izit 1st row and 3rd row combine, 2nd and 4th row combone?or just 1st and 2nd combine, 3rd and 4th combine Hope can help...but advise to wait pros with confirmation... |
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-07-17 : 06:13:39
|
No, it is not based on the rows. it is based on names. I will give input as 2 names. What ever the names i gave, it has to get the count of both boys & girls. For example, from above data. if i give combination as Saurabh and kumar then output should beName Boys GirlsSaurabh Kumar 62.20 53the query should be like Select sum(boys), sum(girls) where Name = 'saurabh' or name = 'kumar'.But my problem is conversion...quote: Originally posted by waterduck
quote: combination of names
what do you mean that? izit 1st row and 3rd row combine, 2nd and 4th row combone?or just 1st and 2nd combine, 3rd and 4th combine Hope can help...but advise to wait pros with confirmation...
G. Satish |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-17 : 06:18:45
|
Hiconvertion problem means .. i think you will not get decimal point right...-------------------------R.. |
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-07-17 : 06:24:25
|
I dont know, whether my query is right or not for my requirement. But i wrote as per my thought. But it giving following error.Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to numeric.if you are confused....leave my query....and give fresh query for my requirement.G. Satish |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-07-17 : 06:25:33
|
[code]CREATE TABLE #trysum2(name varchar(10),boy int,girl int)INSERT INTO #trysum2SELECT 'AAA', 1, 2 UNION ALLSELECT 'BBB', 3, 4 SELECT (SELECT name FROM #trysum2 WHERE name = 'AAA')+' '+(SELECT name FROM #trysum2 WHERE name = 'BBB'), SUM(BOY), SUM(GIRL)FROM #trysum2WHERE name = 'AAA' or name = 'BBB'DROP TABLE #trysum2[/code]>"< this solution eat i/o speed Hope can help...but advise to wait pros with confirmation... |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-07-17 : 06:34:59
|
quote: Originally posted by waterduck
CREATE TABLE #trysum2(name varchar(10),boy int,girl int)INSERT INTO #trysum2SELECT 'AAA', 1, 2 UNION ALLSELECT 'BBB', 3, 4 SELECT (SELECT name FROM #trysum2 WHERE name = 'AAA')+' '+(SELECT name FROM #trysum2 WHERE name = 'BBB'), SUM(BOY), SUM(GIRL)FROM #trysum2WHERE name = 'AAA' or name = 'BBB'DROP TABLE #trysum2 >"< this solution eat i/o speed Hope can help...but advise to wait pros with confirmation...
If you pass parameters, it can bedeclare @name1 varchar(20), @name2 varchar(20)select @name1='AAA',@name2='BBB'SELECT @name1+' '+@name2,sum(Boy),sum(girl) FROM #trysum2WHERE name = 'AAA' or name = 'BBB'MadhivananFailing to plan is Planning to fail |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-07-17 : 06:36:57
|
eh....ya wor!!! i forgot passing parameter can join...so sorry... Hope can help...but advise to wait pros with confirmation... |
|
|
satish.gorijala
Posting Yak Master
182 Posts |
Posted - 2009-07-17 : 06:47:02
|
Hi Guys, My query is working fine but only one problem. If both the names contain data without decimals then it getting below error"Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to numeric."What i mean is if i took names ASPEN,AARENName Boy GirlASPEN 10 11AAREN 20 nullif data is like above i am getting error.. otherwise if the data is like any one of the boy among those two contains decimal value. ie ASPEN 10.5AAREN 11I am getting resultASPEN 10.5AAREN 11.5In above case also i am getting result.But only case is both having non dcimal values i am getting errorSelect sum(Convert(decimal(10,2),boy)) as B, sum(Convert(decimal(10,2),girl)) as G from TBL_Genderwhere Name = 'ASPEN' or Name = 'AAREN'G. Satish |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2009-07-17 : 11:40:38
|
[code]Select sum(Convert(decimal(10,2),ISNULL(boy, 0))) as B, sum(Convert(decimal(10,2),ISNULL(girl, 0))) as G from TBL_Genderwhere Name = 'ASPEN' or Name = 'AAREN'[/code]use 'ISNULL' function to replace NULL with 0 ^^ Hope can help...but advise to wait pros with confirmation... |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-07-20 : 02:57:35
|
<<use 'ISNULL' function to replace NULL with 0 ^^>>Why are you always using some special characters like ^^, >"< ,etc? MadhivananFailing to plan is Planning to fail |
|
|
|