Need help in query - SQL Server Forums

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)

Need help in query

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-23 : 03:45:44

My Query and output as below

Select t.Name, t.BDvalue, a.BDvalue as BDsum
from TBL_Analysis t
join
(select Name, BDvalue = sum(BDvalue) from
(select Name, BDvalue from TBL_Analysis) a
group by Name) a on t.Name = a.Name

Name BDvalue BDsum
Jayme 34 68
JAYME 34 68
Bordenave 39 78
BORDENAVE 39 78
GLASCOE 48 142
GLASCOE 47 142
GLASCOE 47 142

My requirement is need to add values of BDvalues column based on Name column. For the same names, i need to make sum of those corresponding BDvalues. If Name is "Jayme" the sum of those values(34+34) getting right for this. If the name contains different values. i.e (48+47+47) instead of getting result as 142. i need it as 95.
How can i change the query for this.

G. Satish

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-06-23 : 03:50:03

select name,sum(BDvalue )over(partition by name,BDvalue)from table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 03:57:51

select name, bdvalue, sum(BDvalue) over (partition by name) from table

E 12°55'05.63"
N 56°04'39.26"

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-23 : 04:59:39

I didn't get my answer through your query. i will give my requirement clearly.

I have one table with following data

Name Group BDvalue
GLASCOE A 47
GLASCOE F 48
GLASCOE K 47
Jayme L 34
Krish H 78
Jayme O 34

Now i want to sum the BDvalues of the same names. If the name has disticnt BD values, add only distinct values.
i.e for Name 'GLASCOE' we need to add 47+ 48 = 95 instead of getting (47+48+47)142. Similarly for Name 'Jayme'
we need to get BDsum value as 68. Need query for this.. I tried but getting wrong results

The Output should be as follows

Name Group BDvalue BDSum
GLASCOE A 47 95
GLASCOE F 48 95
GLASCOE K 47 95
Jayme L 34 68
Krish H 78 78
Jayme O 34 68

quote:
Originally posted by Peso

select name, bdvalue, sum(BDvalue) over (partition by name) from table

E 12°55'05.63"
N 56°04'39.26"

G. Satish

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 05:18:53

[code]DECLARE @Sample TABLE
(
Name VARCHAR(20),
[Group] CHAR(1),
BDvalue INT
)

INSERT @Sample
SELECT 'GLASCOE', 'A', 47 UNION ALL
SELECT 'GLASCOE', 'F', 48 UNION ALL
SELECT 'GLASCOE', 'K', 47 UNION ALL
SELECT 'Jayme', 'L', 34 UNION ALL
SELECT 'Krish', 'H', 78 UNION ALL
SELECT 'Jayme', 'O', 34

SELECT s.Name,
s.[Group],
s.BDValue,
CASE w.r
WHEN 1 THEN w.q2
ELSE w.q1
END AS BDSum
FROM @Sample AS s
INNER JOIN (
SELECT Name,
COUNT(DISTINCT BDValue) AS r,
SUM(DISTINCT BDValue) AS q1,
SUM(BDValue) AS q2
FROM @Sample
GROUP BY Name
) AS w ON w.Name = s.Name
ORDER BY s.Name,
s.[Group][/code]

E 12°55'05.63"
N 56°04'39.26"

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-23 : 05:29:31

Its perfect !!. Thank you Peso.

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		Name VARCHAR(20),
		[Group] CHAR(1),
		BDvalue INT
	)

INSERT	@Sample
SELECT	'GLASCOE', 'A', 47 UNION ALL
SELECT	'GLASCOE', 'F', 48 UNION ALL
SELECT	'GLASCOE', 'K', 47 UNION ALL
SELECT	'Jayme', 'L', 34 UNION ALL
SELECT	'Krish', 'H', 78 UNION ALL
SELECT	'Jayme', 'O', 34

SELECT		s.Name,
		s.[Group],
		s.BDValue,
		CASE w.r
			WHEN 1 THEN w.q2
			ELSE w.q1
		END AS BDSum
FROM		@Sample AS s
INNER JOIN	(
			SELECT		Name,
					COUNT(DISTINCT BDValue) AS r,
					SUM(DISTINCT BDValue) AS q1,
					SUM(BDValue) AS q2
			FROM		@Sample
			GROUP BY	Name
		) AS w ON w.Name = s.Name
ORDER BY	s.Name,
		s.[Group]

E 12°55'05.63"
N 56°04'39.26"

G. Satish

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-23 : 05:31:01

Ah peso -- there first yet again!

mine is slightly different

DECLARE @foo TABLE (
	[name] NVARCHAR(255)
	, [group] NCHAR(1)
	, [bdValue] INT
	)
INSERT @foo
      SELECT 'GLASCOE',	 'A',	 47
UNION SELECT 'GLASCOE',	 'F',	 48
UNION SELECT 'GLASCOE',	 'K',	 47
UNION SELECT 'Jayme',	 'L',	 34
UNION SELECT 'Krish,',	 'H',	 78
UNION SELECT 'Jayme',	 'O',	 34

SELECT
	f.[name]
	, f.[group]
	, f.[bdValue]
	, fs.[bdVal] AS [DistinctSum]
FROM
	@foo f
	JOIN (
		SELECT
			dist.[name] AS [name]
			, SUM(dist.[value]) AS [bdVal]
		FROM
			(
			SELECT DISTINCT
				[name] AS [name]
				, [bdValue] AS [value]
			FROM
				@foo
			)
			dist
		GROUP BY
			dist.[name]
		)
		fs ON fs.[name] = f.[name]

/* Desired Output
Name	 Group	BDvalue	BDSum
GLASCOE	 A	 47	 95
GLASCOE	 F	 48	 95
GLASCOE	 K	 47	 95
Jayme	 L	 34	 68
Krish	 H	 78	 78
Jayme	 O	 34	 68
*/

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 07:10:11

Your value for Jayme needs a slight adjustment...

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources