Help in query - Logic changed to prev post

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)

Help in query - Logic changed to prev post

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-06-25 : 02:11:15

Hi, i have following data in the data table

Name Group BDvalue Gender BDSum
Aaberg A 94 M
Aaverg A 94 M
Krish D 89 F
Krish E 89 M
Jayme F 32 M
Jayme F 12 F
Jayme R 78 F

I need out as below. Need to consider 3 columns data(Name, Group,BDValue). If the data of these 3 columns is same then Put BDvalue data in to column BDSum.(Example for this is Aaberg)
If any of the three columns data has been changed then add BD values of particaulr rows and put that value in BDSum(89 + 89).

Name Group BDvalue Gender BDSum
Aaberg A 94 M 94
Aaverg A 94 M 94
Krish D 89 F 178
Krish E 89 M 178
Jayme F 32 M 122
Jayme F 12 F 122
Jayme R 78 F 122

My Original query is

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

Peso Replied for my previous post and gave query but now requirement is changed

G. Satish

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 02:55:13

SELECT Name, [Group], BDValue, Gender, SUM(BDValue) OVER (PARTITION BY Name) AS BDSum
FROM Table1

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

sql117
Starting Member

19 Posts

Posted - 2009-06-25 : 03:16:00

Hi, this is satish, login with differnt name now..some problem with my old userid. unable to login....

I think you didn;t get my question clearly. i will explain here. Following is the table with test data

Name Group BDvalue BDSum
Aaberg A 94
Aaverg A 94
Krish D 89
Krish E 89
Jayme F 32
Jayme F 12
Jayme R 78

First of all pick the data with same names.for example 'Aaberg' is the name with two rows. Now i need to fill BDsum column based on remaning 2 columns. Check Whether Group and BDvalue. if it is same for two rows, use same value for BDsum. In this case, Group = A and BDvalue = 94. Both are same. so use 94 to BDsum.

Now took another name Krish. Here Group is diff and Bdvalue is same. Then add BDvalue and make it as BDsum(89+89 = 188).

Now took another name Jayme. Here group is diff and Bdvalue is diff then add Bdvalues and make it as BDsum(32+12+78 = 122).

If either group or Bdvalue is differnet then add Bdvalues otherwise use same BDvalue for BDsum column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 03:30:22

So, EITHER group by (Name), or group by (Group and BDValue)?

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

sql117
Starting Member

19 Posts

Posted - 2009-06-25 : 03:32:44

quote:
Originally posted by sql117

Hi, this is satish, login with differnt name now..some problem with my old userid. unable to login....

I think you didn;t get my question clearly. i will explain here. Following is the table with test data

Name Group BDvalue BDSum
Aaberg A 94
Aaverg A 94
Krish D 89
Krish E 89
Jayme F 32
Jayme F 12
Jayme R 78

First of all pick the data with same names.for example 'Aaberg' is the name with two rows. Now i need to fill BDsum column based on remaning 2 columns. Check Whether Group and BDvalue. if it is same for two rows, use same value for BDsum. In this case, Group = A and BDvalue = 94. Both are same. so use 94 to BDsum.

Now took another name Krish. Here Group is diff and Bdvalue is same. Then add BDvalue and make it as BDsum(89+89 = 188).

Now took another name Jayme. Here group is diff and Bdvalue is diff then add Bdvalues and make it as BDsum(32+12+78 = 122).

If either group or Bdvalue is differnet then add Bdvalues otherwise use same BDvalue for BDsum column.

Peso, Previously you have given one query for the requirement. That is only based on BDvalue column. I am sure..need to do some modificatino on query to get the result on my current requirement. i tired a lot.but not getting.. Below is your's previous reply

Sum
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]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 03:38:04

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

INSERT @Sample
SELECT 'Aaberg', 'A', 94, 'M', 94 UNION ALL
SELECT 'Aaverg', 'A', 94, 'M', 94 UNION ALL
SELECT 'Krish', 'D', 89, 'F', 178 UNION ALL
SELECT 'Krish', 'E', 89, 'M', 178 UNION ALL
SELECT 'Jayme', 'F', 32, 'M', 122 UNION ALL
SELECT 'Jayme', 'F', 12, 'F', 122 UNION ALL
SELECT 'Jayme', 'R', 78, 'F', 122

SELECT s.*,
COALESCE(n.BDSum, gb.BDSum) AS PesoSum
FROM @Sample AS s
LEFT JOIN (
SELECT Name,
SUM(BDValue) AS BDSum
FROM @Sample
GROUP BY Name
HAVING COUNT(*) > 1
) AS n ON n.Name = s.Name
LEFT JOIN (
SELECT Grp,
BDValue,
MAX(BDValue) AS BDSum
FROM @Sample
GROUP BY Grp,
BDValue
HAVING COUNT(*) > 1
) AS gb ON gb.Grp = s.Grp
AND gb.BDValue = s.BDValue[/code]

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

sql117
Starting Member

19 Posts

Posted - 2009-06-25 : 03:38:22

Peso, Previously you have given one query for the requirement. That is only based on BDvalue column. I am sure..need to do some modificatino on query to get the result on my current requirement. i tired a lot.but not getting.. Below is your's previous reply

Sum
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]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 03:46:33

What about the new reply made 06/25/2009 : 03:38:04 ?

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

sql117
Starting Member

19 Posts

Posted - 2009-06-25 : 04:37:16

With the latest query...some of the data is missing..i got the results for one name as follows

Name Group Bdvalue Bdsum
GLASCOE A 47 142
GLASCOE A 47 142

But original data for this name is

Name Group Bdvalue Bdsum
GLASCOE A 47
GLASCOE B 48
GLASCOE A 47

Another example is for name 'aba'

Name Group Bdvaalue Bdsum
ABA B 25 70
ABA F 31 70

The orginal data is

Name Group Bdvaalue Bdsum
ABA A 14
ABA B 25
ABA F 31

I think its getting recrods of only one group...

quote:
Originally posted by Peso

What about the new reply made 06/25/2009 : 03:38:04 ?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 04:50:09

Where do Glasgoe come from? That is not part of your sample data?

Are you able to provide proper and accurate sample data for us to test with?

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

sql117
Starting Member

19 Posts

Posted - 2009-06-25 : 05:06:40

I run the query on my original data. the two records "ABA" and "GLASCOE" are both from original data.

Really i am getting frustrated today... When i look in to the data its be simple to get out the results. but the way to get it i.e querying is more complicated.

quote:
Originally posted by Peso

Where do Glasgoe come from? That is not part of your sample data?

Are you able to provide proper and accurate sample data for us to test with?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 05:13:05

If it's that simple when you can LOOK at the data, why don't you do this for yourself?
Remember that we can't look at your data. We don't have access to your server(s).

How about posting ALL sample data to us for testing our solutions?
It is annoying to give you so much time with posting a solution and then later get a reply "It is not working".

Huh? It is working with the sample data you have provided. If you have more sample data, well, the it's up to you if you want help or not.
Come on, how hard is it to post proper sample data?

First read this blog post WHY you should post proper sample data
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

And then read this blog post HOW to post proper sample data
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

Gokila
Starting Member

2 Posts

Posted - 2011-05-27 : 02:36:30

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[Sp_ReportStudContacts]
(
@ClassID bigint,
@SectionID bigint,
@StudType varchar(50)
)

As

SELECT
CM.ClassName,
SM.SectionName,
SD.StudentCode,
SD.StudentName,
SD.PresentAddress,
SD.PermenantAddress,
SD.PhoneNo,
SD.Hostler,
SD.ClassID,
SD.SectionID,
SD.StudentID

FROM StudentDetails SD INNER JOIN
ClassMaster CM ON SD.ClassID=CM.ClassID INNER JOIN
SectionMaster SM ON SD.SectionID=SM.SectionID

WHERE (SD.ClassID=@ClassID) AND (SD.SectionID=@SectionID) AND (CONVERT(VARCHAR(50),SD.Hostler)=CONVERT(VARCHAR(50),@Hostler))

--EXEC Sp_ReportStudContacts @ClassID='1',@SectionID='1'

This is my code it shows some error like

Msg 137, Level 15, State 2, Procedure Sp_ReportStudContacts, Line 28
Must declare the scalar variable "@Hostler".

R.Gokila

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-05-27 : 04:00:01

As the error message says, you must declare the variable @Hostler in your code.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources