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
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
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy