February 1, 2011 at 10:06 am
Hello,
I a trying to sum up numeric data in newly created columns (the columns themselves have been created from existing columns). The sum of the data should reflect in a newly created column
Does anyone know how this can be achieved ?
Kind Regards,
Paul
February 1, 2011 at 11:37 am
To help those who want to help you with a tested solution, please post table definition(s), sample data and required results. You can do this easily, just click on the first link in my signature block and follow the techniques/T-SQL contained in the displayed article.
February 2, 2011 at 5:04 am
Hello Ron,
Thanks a lot for your reply. I have a query where I need to include the sum of rows per Booking ID. The data for all the columns - 20DRY to 40OTH needs to be added up and get displayed in a new column, TEU. The 20DRY to 40OTH columns have been created using the columns, EQ_CNT and KL_EQUIPMENT_TYPE_CD.
This would also be based on the condition that If KL_EQUIPMENT_TYPE_CD like '20%' then multiply x 1, if like '40%' or 45% then multiply x2. TEU will then be sum of all these calculations.
Below are the DDL and sample data for the 2 tables-
1. MG_BOOKING
ddl-
CREATE TABLE [dbo].[MG_BOOKING](
[BOOKING_ID] [numeric](10, 0) NOT NULL
)
The sample data for above table:
----------------------------------------------
Insert into MG_BOOKING
Values('639')
Insert into MG_BOOKING
Values('962')
Insert into MG_BOOKING
Values('964')
Insert into MG_BOOKING
Values('1061')
Insert into MG_BOOKING
Values('1085')
Insert into MG_BOOKING
Values('3137')
Insert into MG_BOOKING
Values('5477')
Insert into MG_BOOKING
Values('7228')
Insert into MG_BOOKING
Values('8132')
Insert into MG_BOOKING
Values('2125743')
------------------------------------
2. MG_BOOKING_EQUIPMENT_REQ
ddl-
CREATE TABLE [dbo].[MG_BOOKING_EQUIPMENT_REQ](
[BOOKING_ID] [numeric](10, 0) NULL,
[EQ_CNT] [numeric](3, 0) NULL,
[KL_EQUIPMENT_TYPE_CD] [varchar](5) NULL
)
Sample data for the above table:
-------------------------------------------
Insert into MG_BOOKING_EQUIPMENT_REQ
values('639','2','40D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('962','2','40R96')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('964','1','20D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('1061','1','20D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('1085','1','20D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('3137','2','40D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('5477','2','40D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('7228','1','20D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('8132','3','20D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('2125743','1','40D86')
------------------------------------------
Please also find the query attached which is used to display data for the columns 20DRY to 40OTH. In this query itself, the new column, TEU needs to be created which will display the sum of all the data in the columns- 20DRY to 40OTH.
Kind Regards,
Paul
February 2, 2011 at 10:52 am
pwalter83
I have posted your code in a easily usable format for others to assist you. To be honest with you my knowledge is not that vast and I do not think I can be of much assistance.
I will request other more knowledgable to view your posting, to determine if they can be of more assistance.
CREATE TABLE [dbo].[MG_BOOKING](
[BOOKING_ID] [numeric](10, 0) NOT NULL
)
Insert into MG_BOOKING
Values('639')
Insert into MG_BOOKING
Values('962')
Insert into MG_BOOKING
Values('964')
Insert into MG_BOOKING
Values('1061')
Insert into MG_BOOKING
Values('1085')
Insert into MG_BOOKING
Values('3137')
Insert into MG_BOOKING
Values('5477')
Insert into MG_BOOKING
Values('7228')
Insert into MG_BOOKING
Values('8132')
Insert into MG_BOOKING
Values('2125743')
CREATE TABLE [dbo].[MG_BOOKING_EQUIPMENT_REQ](
[BOOKING_ID] [numeric](10, 0) NULL,
[EQ_CNT] [numeric](3, 0) NULL,
[KL_EQUIPMENT_TYPE_CD] [varchar](5) NULL
)
Insert into MG_BOOKING_EQUIPMENT_REQ
values('639','2','40D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('962','2','40R96')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('964','1','20D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('1061','1','20D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('1085','1','20D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('3137','2','40D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('5477','2','40D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('7228','1','20D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('8132','3','20D86')
Insert into MG_BOOKING_EQUIPMENT_REQ
values('2125743','1','40D86')
You query from the attachement with those rows between a /* and a */ deleted
SELECT top 200 MGB.BOOKING_ID, MBEQ.KL_EQUIPMENT_TYPE_CD, MBEQ.EQ_CNT,
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '20D86' THEN MBEQ.EQ_CNT END AS [20DRY] ,
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '40D86' THEN MBEQ.EQ_CNT END AS [40DRY] ,
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '40D96' THEN MBEQ.EQ_CNT END AS [40DHC] ,
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '45D96' THEN MBEQ.EQ_CNT END AS [45DRY] ,
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '20R86' THEN MBEQ.EQ_CNT END AS [20REF] ,
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '40R86' THEN MBEQ.EQ_CNT END AS [40REF] ,
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '40R96' THEN MBEQ.EQ_CNT END AS [HCREF] ,
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '20O86' THEN MBEQ.EQ_CNT END AS [20OTP],
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '40O86' THEN MBEQ.EQ_CNT END AS [40OTP],
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '20F86' THEN MBEQ.EQ_CNT END AS [20FLR],
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '40F86' THEN MBEQ.EQ_CNT END AS [40FLR],
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD NOT IN ('20D86', '20R86', '20O86', '20F86') THEN MBEQ.EQ_CNT END AS [20OTH],
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD NOT IN ('40D86', '40D96', '45D96', '40R86', '40R96', '40O86', '40F86') THEN MBEQ.EQ_CNT END AS [40OTH],
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '20D86' THEN MBEQ.EQ_CNT * 1 END +
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '40D86' THEN MBEQ.EQ_CNT * 2 END +
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '40D96' THEN MBEQ.EQ_CNT * 2 END +
CASE WHEN MBEQ.KL_EQUIPMENT_TYPE_CD = '45D96' THEN MBEQ.EQ_CNT * 2 END
AS TEU
FROM MG_BOOKING MGB
INNER JOIN MG_BOOKING_EQUIPMENT_REQ MBEQ
ON MGB.BOOKING_ID = MBEQ.BOOKING_ID
where MGB.BOOKING_ID in ('639','962','964','1061','1085','3137','5477','7228','8132','2125743')
February 2, 2011 at 11:49 am
I can't see from the information provided whether the query will actually deliver a value for all the columns in each row - I'm pretty sure it won't, but a detailed check would be time consuming. If this were my problem, the first thing I would do is rejig the code so that each of the relevant columns is defined by a single case statement that includes a default (0).
It then becomes very simple to define TEU within the query (if I've understood the requirement correctly):
,([20DRY]+[40DRY]+[40DHC]+[45DRY]+[20REF]+[40REF]+[HCREF]+[20OTP]+[40OTP]+[20FLR]+
[40FLR]+[20OTH]+[40OTH]) *
case when KL_EQUIPMENT_TYPE_CD like '20%' the 1 else 2 end as [TEU]
Tom
February 3, 2011 at 2:21 am
Hello Tom,
Thanks a lot for your solution. It was really helpful...
Regards,
Paul
February 3, 2011 at 2:22 am
Thanks a lot for your help, Ron !!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply