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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  General SQL Server Forums  New to SQL Server Programming  Summing Across Rows Based on Formula Reply to Topic  Printer Friendly
Author  Topic

BobRoberts
Posting Yak Master

USA
109 Posts

 Posted - 02/01/2013 :  16:56:50 ``` It's hard even to describe this one, except to say I'm having trouble finding a SQL approach to it. I've created a minimal type of example to illustrate the basic idea, and so I should be able to apply any solution to the real data. I have one table TBL_DETAIL whose contents I want to use to update TBL_SUMMARY. For the TBL_DETAIL, it's important to note that the number of rows and the contents of the LOCATION and TYPE columns will never change. The only thing that will change are the values themselves in the AAA, BBB, CCC, and DDD columns. The number of rows and contents of the SUMLOC column in the TBL_SUMMARY also will not change, but the SUMTOTAL column will contain the calculated value from the TBL_DETAIL. Those values come from AAA, BBB, CCC, and DDD according to this formula: (APPLES.AAA - (ORANGES.AAA + PEARS.AAA)) * 2 + APPLES.BBB - (ORANGES.BBB + PEARS.BBB) + (APPLES.DDD - (ORANGES.DDD + PEARS.DDD)) * 2 The above formula filled in with values from the below table would look like: For B1: ((50 - (6 + 1)) * 2) + (60 - (10 + 2)) + ((80 - (20 + 18)) * 2) The total of the above for B1 would be 218. For MAIN: ((100 - (22 + 27)) * 2) + (200 - (9 + 13)) + ((400 - (24 + 19)) * 2) The total of the above for MAIN would be 994. Here is the data in the TBL_DETAIL table: LOCATION--TYPE-----AAA--BBB--CCC--DDD B1--------APPLES---050--060--070--080 B1--------GRAPES---007--012--008--016 B1--------ORANGES--006--010--019--020 B1--------PEARS----001--002--003--018 MAIN------APPLES---100--200--300--400 MAIN------GRAPES---026--016--002--031 MAIN------ORANGES--022--009--014--024 MAIN------PEARS----027--013--008--019 And the data in the TBL_SUMMARY table before the query: SUMLOC--SUMTOTAL B1------00000000 MAIN----00000000 After the query, the TBL_SUMMARY table should look like: SUMLOC--SUMTOTAL B1------00000218 MAIN----00000994 But the big mystery is, what would the query look like? Forgot to include the test table creation script, which might come in handy: USE [Northwind] GO /****** Object: Table [dbo].[TBL_DETAIL] Script Date: 02/02/2013 11:27:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TBL_DETAIL]( [LOCATION] [varchar](10) NULL, [TYPE] [varchar](20) NULL, [AAA] [int] NULL, [BBB] [int] NULL, [CCC] [int] NULL, [DDD] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'B1', N'APPLES', 50, 60, 70, 80) INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'B1', N'GRAPES', 7, 12, 8, 16) INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'B1', N'ORANGES', 6, 10, 19, 20) INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'B1', N'PEARS', 1, 2, 3, 18) INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'MAIN', N'APPLES', 100, 200, 300, 400) INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'MAIN', N'GRAPES', 26, 16, 2, 31) INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'MAIN', N'ORANGES', 22, 9, 14, 24) INSERT [dbo].[TBL_DETAIL] ([LOCATION], [TYPE], [AAA], [BBB], [CCC], [DDD]) VALUES (N'MAIN', N'PEARS', 27, 13, 8, 19) /****** Object: Table [dbo].[TBL_SUMMARY] Script Date: 02/02/2013 11:27:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TBL_SUMMARY]( [SUMLOC] [varchar](10) NULL, [SUMTOTAL] [int] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT [dbo].[TBL_SUMMARY] ([SUMLOC], [SUMTOTAL]) VALUES (N'B1', 218) INSERT [dbo].[TBL_SUMMARY] ([SUMLOC], [SUMTOTAL]) VALUES (N'MAIN', 994) ``` Edited by - BobRoberts on 02/02/2013 12:34:29

James K
Flowing Fount of Yak Knowledge

3873 Posts

 Posted - 02/01/2013 :  17:33:29 Do you have only 3 possible values in the TYPE column and four columns, or is this just an example (and the real data has many more TYPEs and many more columns?

bitsmed
Aged Yak Warrior

545 Posts

 Posted - 02/01/2013 :  17:45:07 Maybe this will work:```select location ,(sum(case when type='APPLES' then aaa else 0 end)-sum(case when type='ORANGES' or type='PEARS' then aaa else 0 end))*2 + sum(case when type='APPLES' then bbb else 0 end)-sum(case when type='ORANGES' or type='PEARS' then bbb else 0 end) +(sum(case when type='APPLES' then ddd else 0 end)-sum(case when type='ORANGES' or type='PEARS' then ddd else 0 end))*2 from tbl_detail group by location```

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 02/02/2013 :  03:06:33 ``` UPDATE s SET s.SUMTOTAL = s.SUMTOTAL + d.Total FROM TBL_SUMMARY s INNER JOIN (SELECT location, (SUM(CASE WHEN TYPE = 'APPLES' THEN AAA ELSE -1 * AAA END) * 2) + SUM(CASE WHEN TYPE = 'APPLES' THEN BBB ELSE -1 * BBB END) + (SUM(CASE WHEN TYPE = 'APPLES' THEN DDD ELSE -1 * DDD END) * 2) AS Total FROM TBL_DETAIL WHERE TYPE IN ('APPLES','ORANGES','PEARS') GROUP BY location )d ON d.location = s.location ```------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/

BobRoberts
Posting Yak Master

USA
109 Posts

 Posted - 02/02/2013 :  12:44:17 quote:Originally posted by James KDo you have only 3 possible values in the TYPE column and four columns, or is this just an example (and the real data has many more TYPEs and many more columns?That's a good question. The real data has 9 types and 12 columns, but they never vary - they are always there and in the same sequence, so I figured that reducing it to 3 TYPE's and 4 columns would retain the essence of the task.

BobRoberts
Posting Yak Master

USA
109 Posts

 Posted - 02/02/2013 :  13:06:24 Yes, your solution works perfectly in coming up with the column counts, but part of the problem as stated is updating the TBL_SUMMARY table with those counts. I've tried a few things, and I'm having some trouble doing that.quote:Originally posted by bitsmedMaybe this will work:```select location ,(sum(case when type='APPLES' then aaa else 0 end)-sum(case when type='ORANGES' or type='PEARS' then aaa else 0 end))*2 + sum(case when type='APPLES' then bbb else 0 end)-sum(case when type='ORANGES' or type='PEARS' then bbb else 0 end) +(sum(case when type='APPLES' then ddd else 0 end)-sum(case when type='ORANGES' or type='PEARS' then ddd else 0 end))*2 from tbl_detail group by location```

BobRoberts
Posting Yak Master

USA
109 Posts

 Posted - 02/02/2013 :  13:44:05 Yes, this one does the trick completely. Thanks once again.quote:Originally posted by visakh16``` UPDATE s SET s.SUMTOTAL = s.SUMTOTAL + d.Total FROM TBL_SUMMARY s INNER JOIN (SELECT location, (SUM(CASE WHEN TYPE = 'APPLES' THEN AAA ELSE -1 * AAA END) * 2) + SUM(CASE WHEN TYPE = 'APPLES' THEN BBB ELSE -1 * BBB END) + (SUM(CASE WHEN TYPE = 'APPLES' THEN DDD ELSE -1 * DDD END) * 2) AS Total FROM TBL_DETAIL WHERE TYPE IN ('APPLES','ORANGES','PEARS') GROUP BY location )d ON d.location = s.location ```------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 02/03/2013 :  10:58:53 welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC