Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how can i get comibination count

Author  Topic 

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-17 : 06:03:53
The data in my table is as follows

Name Boys Girls
Saurabh 50.20 30
Santhi 80 7
Satish 90 10.10
Kumar 12 23

Here 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.10
Santhi Saurabh 130.20 37

I 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_gender
where 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...
Go to Top of Page

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 be
Name Boys Girls
Saurabh Kumar 62.20 53

the 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
Go to Top of Page

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-17 : 06:18:45
Hi

convertion problem means .. i think you will not get decimal point right...

-------------------------
R..
Go to Top of Page

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 1
Error converting data type varchar to numeric.

if you are confused....leave my query....and give fresh query for my requirement.

G. Satish
Go to Top of Page

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 #trysum2
SELECT 'AAA', 1, 2 UNION ALL
SELECT 'BBB', 3, 4

SELECT (SELECT name FROM #trysum2 WHERE name = 'AAA')+' '+(SELECT name FROM #trysum2 WHERE name = 'BBB'), SUM(BOY), SUM(GIRL)
FROM #trysum2
WHERE 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...
Go to Top of Page

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 #trysum2
SELECT 'AAA', 1, 2 UNION ALL
SELECT 'BBB', 3, 4

SELECT (SELECT name FROM #trysum2 WHERE name = 'AAA')+' '+(SELECT name FROM #trysum2 WHERE name = 'BBB'), SUM(BOY), SUM(GIRL)
FROM #trysum2
WHERE 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 be


declare @name1 varchar(20), @name2 varchar(20)
select @name1='AAA',@name2='BBB'

SELECT @name1+' '+@name2,sum(Boy),sum(girl)
FROM #trysum2
WHERE name = 'AAA' or name = 'BBB'


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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...
Go to Top of Page

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 1
Error converting data type varchar to numeric."

What i mean is if i took names ASPEN,AAREN
Name Boy Girl
ASPEN 10 11
AAREN 20 null

if 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.5
AAREN 11

I am getting result

ASPEN 10.5
AAREN 11.5

In above case also i am getting result.

But only case is both having non dcimal values i am getting error



Select sum(Convert(decimal(10,2),boy)) as B,
sum(Convert(decimal(10,2),girl)) as G from TBL_Gender
where Name = 'ASPEN' or Name = 'AAREN'

G. Satish
Go to Top of Page

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_Gender
where Name = 'ASPEN' or Name = 'AAREN'[/code]
use 'ISNULL' function to replace NULL with 0 ^^


Hope can help...but advise to wait pros with confirmation...
Go to Top of Page

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?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -