January 7, 2011 at 9:21 am
Hello,
I am facing a problem in my query. The select statement in my query includes the sum of a column(frt_usd). In order for the query to execute, I need to include all the columns(in the select ) in the group by statement. However, as per the business rquirement, I only need to include one column(bl_id) in the group by.
Including all the columns in the select statement does not achieve the desired resultset.
Can someone please help me with this ? Please find my sample query attached.
Kind Regards,
Paul
January 7, 2011 at 9:24 am
What end result are you trying to achieve? We don't know your business rules, so it's hard to tell what to suggest regarding the query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 10, 2011 at 2:59 am
Hi GSquared,
Thanks for your reply. What I am trying to do is join two tables to display columns(5 in total) from both the tables and populate it in a resulting table. One of the fields is a sum(TEU) and in order to achieve this I need to use a group by statement.
As per the business requirement the sum of TEU is to be calculated per BL_ID(one of the 5 columns). But as I have to display all the 5 columns and I need to use all the five fields in the group by. If I do this, I don't get the desired result as it produces duplicate values. However, the BL_ID field can only have unique values and so it gives out error.
Is there a way that I can only use bl_id in the group by statement and avoid others.
Please find the the table structure for the 3 tables that I am using below -
First one is the DHL_TRADE_ASSIGNMENT table-
-----------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DHL_TRADE_ASSIGNMENT](
[COUNTRY_CD] [nvarchar](20) NOT NULL,
[COUNTRY_DSC] [nvarchar](50) NULL,
[REGION_TRADE] [nvarchar](50) NULL,
[SUB_REGION] [nvarchar](50) NULL,
UNIQUE NONCLUSTERED
(
[COUNTRY_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------
Sample data for DHL_TRADE_ASSIGNMENT-
-----------------------------------------------------------------------
insert into DHL_TRADE_ASSIGNMENT
values('FRS', 'France South', 'EURO MED', 'EURO MED');
insert into DHL_TRADE_ASSIGNMENT
values('FRN', 'France North', 'EURO NC', 'EURO NC');
insert into DHL_TRADE_ASSIGNMENT
values('ES', 'SPAIN', 'EURO MED', 'EURO MED');
insert into DHL_TRADE_ASSIGNMENT
values('AT', 'AUSTRIA', 'EURO NC', 'EURO NC');
insert into DHL_TRADE_ASSIGNMENT
values('AU', 'Australia', 'SPAC', 'SPAC');
insert into DHL_TRADE_ASSIGNMENT
values('IN', 'India', 'ASPA', 'ASPA');
insert into DHL_TRADE_ASSIGNMENT
values('RO', 'Romania', 'BLACK SEA', 'BLACK SEA');
insert into DHL_TRADE_ASSIGNMENT
values('CA', 'Canada', 'AMNO', 'AMNO');
insert into DHL_TRADE_ASSIGNMENT
values('AG', 'Antigua', 'AMLA', 'NCSA');
insert into DHL_TRADE_ASSIGNMENT
values('IS', 'Iceland', 'EURO NC', 'OTHERS EURO');
insert into DHL_TRADE_ASSIGNMENT
values('EC', 'EC', 'AMLA', 'WCSA');
insert into DHL_TRADE_ASSIGNMENT
values('CN', 'China', 'ASPA', 'ASPA');
insert into DHL_TRADE_ASSIGNMENT
values('GB', 'Great Britain', 'EURO NC', 'EURO NC');
---------------------------------------------------------------------
2nd table- NCV_BL_DHL_TEMP
-----------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NCV_BL_DHL_TEMP](
[BL_ID] [decimal](10, 0) NOT NULL,
[BL_NUM] [nvarchar](13) NULL,
[CP_GROUP_CD] [nvarchar](30) NULL,
[POL_COUNTRY_CD] [nvarchar](2) NULL,
[POD_COUNTRY_CD] [nvarchar](2) NULL,
[SAISAN_VESSEL_CD] [nvarchar](6) NULL,
[SAISAN_VOYAGE_CD] [nvarchar](6) NULL,
[SAISAN_LEG_CD] [nvarchar](1) NULL,
[DEPART_ACTUAL_DT] [datetime] NULL,
[TEU] [decimal](10, 0) NULL,
PRIMARY KEY CLUSTERED
(
[BL_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------------------------
Sample data for NCV_BL_DHL_TEMP-
-----------------------------------------------------------------------------------------
insert into NCV_BL_DHL_TEMP
values('10286790', 'MAA012330', 'SAIMA', 'IN', 'GB', 'HCMB', '0083W', 'W', '03-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('10296280', 'SH8634919', 'AL_FILTER', 'GB', 'IN', 'XYKOU', '0058W', 'W', '06-05-2010', '2');
insert into NCV_BL_DHL_TEMP
values('10320493', 'GLA007219', 'HANKYU', 'AU', 'AT', 'HANB', '01E', 'E', '02-02-2010', '1');
insert into NCV_BL_DHL_TEMP
values('10442279', 'BLR009972', 'TTP_TECHNOLOGIE', 'AT', 'AU', 'HJCH', '0036W', 'W', '04-05-2010', '1');
insert into NCV_BL_DHL_TEMP
values('10443663', 'LEH901114', 'SAEME', 'FR', 'HK', 'SVB', '648E', 'E', '02-06-2009', '2');
insert into NCV_BL_DHL_TEMP
values('10735227', 'FXT128719', 'FELIANCE_FIBRES', 'TR', 'ES', 'CNTSNG', '258E', 'E', '01-09-2010', '4');
insert into NCV_BL_DHL_TEMP
values('10739501', 'ANR490955', 'DHL_DANZAS', 'BR', 'CA', 'CSGNZU', '006E', 'E', '02-08-2010', '2');
insert into NCV_BL_DHL_TEMP
values('10874599', 'ANR440555', 'DHL_DANZAS', 'CA', 'BR', 'CSGWZU', '006E', 'E', '02-01-2008', '2');
insert into NCV_BL_DHL_TEMP
values('11272481', 'HK1016941', 'OKI', 'CN', 'GB', 'YPULT', '03W', 'W', '02-02-2007', '2');
insert into NCV_BL_DHL_TEMP
values('11852019', 'HK5010911', 'KOK', 'AG', 'IS', 'YMULT', '03W', 'W', '04-06-2008', '2');
insert into NCV_BL_DHL_TEMP
values('11852452', 'HD1086941', 'KPP', 'IS', 'AG', 'YKULT', '03W', 'W', '02-05-2009', '2');
--------------------------------------------------------------------------------------
3rd table- NCV_FREIGHT_DHL_TEMP
----------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NCV_FREIGHT_DHL_TEMP](
[BL_ID] [decimal](10, 0) NOT NULL,
[BL_FREIGHT_ID] [decimal](10, 0) NOT NULL,
[CHARGE_CD] [nvarchar](3) NULL,
[FRT_USD] [decimal](12, 3) NULL,
PRIMARY KEY CLUSTERED
(
[BL_FREIGHT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-----------------------------------------------------------------------------------------
Sample data for NCV_FREIGHT_DHL_TEMP-
-----------------------------------------------------------------------------------------
insert into NCV_FREIGHT_DHL_TEMP
values('10443663', '41640712', 'OFT', '1200.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10735227', '42906574', 'OFT', '665.000');
insert into NCV_FREIGHT_DHL_TEMP
values('10874599', '42913602', 'DOC', '21.730');
insert into NCV_FREIGHT_DHL_TEMP
values('10739501', '42906575', 'PRC', '66.637');
insert into NCV_FREIGHT_DHL_TEMP
values('11852019', '47961402', 'SZC', '36.000');
-------------------------------------------------------------------------------------
Please find my sample query attached. Thanks in advance for your help.
Kind Regards,
Paul
January 10, 2011 at 7:13 am
Are you familiar with inline queries?
You can break up an aggregate query with those.
This is a sample of how they look:
select ID,
(select sum(MyColumn)
from MyOtherTable
where MyTableID = MyTable.ID) as TotalMyColumn
from MyTable;
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply