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
 General SQL Server Forums
 New to SQL Server Programming
 Summing Across Rows Based on Formula

Author  Topic 

BobRoberts
Posting Yak Master

109 Posts

Posted - 2013-02-01 : 16:56:50
[code]
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)


[/code]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-01 : 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?
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-02-01 : 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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-02 : 03:06:33
[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

BobRoberts
Posting Yak Master

109 Posts

Posted - 2013-02-02 : 12:44:17
quote:
Originally posted by James K

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?



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

BobRoberts
Posting Yak Master

109 Posts

Posted - 2013-02-02 : 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 bitsmed

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



Go to Top of Page

BobRoberts
Posting Yak Master

109 Posts

Posted - 2013-02-02 : 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 MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-03 : 10:58:53
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -