February 3, 2011 at 4:03 am
Hello,
In the project that I am working on(creating a dataset), I need to calculate the sum of a field(Gross_Wgt)and then group it by another field(Booking_ID).
The problem is there about 15 other columns and they just need to be displayed as is without any calculation(or using MAX). Is there a way to achieve this without adding MAX to the other columns and also not including other fields in the group by clause ? I just need to sum one field and group by another(one only) field...
As I am using SSIS for this project, is it possible to calculate the Gross Wgt of one field and then add it to the table
Kind Regards,
Paul
February 3, 2011 at 4:09 am
Probably.
But unless you provide a better description of what you are trying to achieve, that's the best answer I can provide.
Here[/url] is a link to an article which will describe how you could pose your question in a way that will make it much easier for others to help you.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 3, 2011 at 4:47 am
Hi Phil,
Thanks for your reply. What I need to achieve is SUM of GROSS_WGT and then GROUP BY BOOKING_ID. The query below would give you the clear picture:
SELECT
SUM(GROSS_WGT) AS GROSS_WGT,
BOOKING_ID,
KL_EQUIPMENT_TYPE_CD,
EQ_CNT,
BOOKING_TYPE_CD,
BOOKING_STATUS_CD,
BOOKING_OFFICE_CD,
BOOKING_SALES_OFFICE_CD,
SERVICE_CD,
PDL_LOCATION_CD,
GENERAL_COMMODITY_CD
FROM tablename
GROUP BY BOOKING_ID
-------------------------------------------
The problem is I cannot just SUM GROSS_WGT and then only GROUP BY BOOKING_ID. I would have to use MAX (or something else) for other columns if I am summing one of the columns. I also need to include the other columns in GROUP BY, which is not the requirement.
So what I asked was is it possible to achieve this using SSIS. I mean just create a table without the GROSS_WGT column and then using SQL task add this column to the table in the next step.
I hope I am clear in explaining the scenario now.
Kind Regards,
Paul
February 3, 2011 at 4:55 am
That's better. You can do that using a subquery - something like this (untested):
SELECT
t2.SumGross,
t.BOOKING_ID,
t.KL_EQUIPMENT_TYPE_CD,
etc etc
FROM tablename t
JOIN
(select t1.Booking_ID, Sum(t1.Gross_Wgt) SumGross
from tablename t1
group by t1.Booking_ID) t2
on t.Booking_ID = t2.BookingID
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 3, 2011 at 5:19 am
Thanks very much for your helpful solution, Phil....I really appreciate it. You have also cleared my long running confusion about this !!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply