Inserting new row by suming data of another rows

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)

Inserting new row by suming data of another rows

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-04-20 : 08:31:56

HI, i have two tables

Table1
-------
Zcode INDI JPAN ASIM Total
165-34 7 2 1 10
165-64 9 3 2 14
165-76 3 3 6 12
165-90 7 2 1 10
567-34 1 9 2 12
567-09 3 4 4 11
435-09 2 5 8 15
435-96 3 4 5 12
768-99 4 4 5 13
145-90 4 2 1 7

Table2
------
Zipcode
165
567
768

Now get first row from table2 and the value is 165. select all the rows from table1 having zcode as like '165%'. Now insert new row to the table1 having Zcode as 165 and for the remining columns data.
Add the rows of 165 which having different totals(i.e 10,14,12 here. Take only one row having total 10).
The final insertion rows should be as below. How can we do this??

Newly inserted rows in table1.
------
Zcode INDI JPAN ASIM Total
165 19 8 9 36
567 4 13 6 23
768 4 4 5 13

G. Satish

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-20 : 08:38:32

This should work:
SELECT SubString(ZCode, 0, 3) as subZCode, SUM(INDI), SUM(JPAN), SUM(ASIM), SUM(Total) FROM [Table1] INNER JOIN [Table2] ON Table1.subZCode = Table2.ZipCode GROUP BY subZCode

I haven't tested it (don't have an SQL editor installed)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 08:47:23

WhiteFang, you problably meant to first DISTINCT all totals as per OP request?

INSERT		Table1
		(
			Zcode,
			INDI,
			JPAN,
			ASIM,
			Total
		)
SELECT		LEFT(d.Zcode, 3) AS Zcode,
		SUM(d.INDI),
		SUM(d.JPAN),
		SUM(d.ASIM),
		SUM(d.Total)
FROM		(
			SELECT	Zcode,
				INDI,
				JPAN,
				ASIM,
				Total,
				ROW_NUMBER() OVER (PARTITION BY Zcode, Total ORDER BY Zcode) AS recID
			FROM	Table1
		) AS d
INNER JOIN	Table2 AS t2 ON t2.Zipcode = d.Zcode
WHERE		d.recID = 1
GROUP BY	LEFT(d.Zcode, 3)

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 09:17:38

We have already posted suggestions to you.
There is no need to edit your original post.

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

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-04-20 : 09:27:00

Sorry, i have given total values as wrong in previous post. The query is ok, its working fine. But it adding all the rows for '165'. I need this as add the rows of having total distinct. Here i need to consider only one row for total having data '10'.
Now i am getting output as 165 30 9 6 45
But i need it as 165 23 7 5 35

quote:

Originally posted by Peso

WhiteFang, you problably meant to first DISTINCT all totals as per OP request?

INSERT		Table1
		(
			Zcode,
			INDI,
			JPAN,
			ASIM,
			Total
		)
SELECT		LEFT(d.Zcode, 3) AS Zcode,
		SUM(d.INDI),
		SUM(d.JPAN),
		SUM(d.ASIM),
		SUM(d.Total)
FROM		(
			SELECT	Zcode,
				INDI,
				JPAN,
				ASIM,
				Total,
				ROW_NUMBER() OVER (PARTITION BY Zcode, Total ORDER BY Zcode) AS recID
			FROM	Table1
		) AS d
INNER JOIN	Table2 AS t2 ON t2.Zipcode = d.Zcode
WHERE		d.recID = 1
GROUP BY	LEFT(d.Zcode, 3)

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

G. Satish

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-20 : 09:34:31

Small mistake of me. Please see this corrected version

DECLARE	@Table1 TABLE
	(
		Zcode VARCHAR(10),
		INDI INT,
		JPAN INT,
		ASIM INT,
		Total INT
	)

INSERT	@Table1
SELECT	'165-34', 7, 2, 1, 10 UNION ALL 
SELECT	'165-64', 9, 3, 2, 14 UNION ALL
SELECT	'165-76', 3, 3, 6, 12 UNION ALL
SELECT	'165-90', 7, 2, 1, 10 UNION ALL
SELECT	'567-34', 1, 9, 2, 12 UNION ALL
SELECT	'567-09', 3, 4, 4, 11 UNION ALL
SELECT	'435-09', 2, 5, 8, 15 UNION ALL
SELECT	'435-96', 3, 4, 5, 12 UNION ALL
SELECT	'768-99', 4, 4, 5, 13 UNION ALL
SELECT	'145-90', 4, 2, 1, 7

DECLARE	@Table2 TABLE
	(
		Zipcode VARCHAR(10)
	)

INSERT	@Table2
SELECT	'165' UNION ALL
SELECT	'567' UNION ALL
SELECT	'768'

--INSERT		Table1
--		(
--			Zcode,
--			INDI,
--			JPAN,
--			ASIM,
--			Total
--		)
SELECT		LEFT(d.Zcode, 3) AS Zcode,
		SUM(d.INDI),
		SUM(d.JPAN),
		SUM(d.ASIM),
		SUM(d.Total)
FROM		(
			SELECT	LEFT(Zcode, 3) AS Zcode,
				INDI,
				JPAN,
				ASIM,
				Total,
				ROW_NUMBER() OVER (PARTITION BY LEFT(Zcode, 3), Total ORDER BY Zcode) AS recID
			FROM	@Table1
		) AS d
INNER JOIN	@Table2 AS t2 ON t2.Zipcode = d.Zcode
WHERE		d.recID = 1
GROUP BY	LEFT(d.Zcode, 3)

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