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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Group by using 2 columns

Author  Topic 

lahsiv2004
Starting Member

13 Posts

Posted - 2015-02-16 : 08:14:54
Hi,

I have a requirement to Group the attached query using only 2 columns. However I am unable to do that without using MAX function or including other columns in the group by statement. I need to add the following statement:

GROUP BY (VESSEL_NAME + VOYAGE_NUM)

Could someone please help in this regard. Thanks.



SELECT VESSEL_NAME,
MG_VSLVOY_HEADER.VOYAGE_NUM,

MG_VSLVOY_HEADER.DELETED_FLG AS header_deletedflag,
MG_VSLVOY_PORT_CONTROL.DELETED_FLG AS portcontrol_deletedflag,
MG_VSLVOY_SCHEDULE.DELETED_FLG AS SCHEDULE_deletedflag,
NATIONALITY_CD AS NATIONALITY_CD,
PORT_CD AS PORT_CD,
CASE
WHEN CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT, 120) = CONVERT(VARCHAR(10), Dateadd(MM, 3, Getdate()), 120)
AND PORT_CD IN ('BEZEE', 'GBSOU')
OR CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.CREATE_DT, 120) = CONVERT(VARCHAR(10), Getdate() - 1, 120)
AND PORT_CD IN ('BEZEE', 'GBSOU')
THEN 'N'
ELSE ''
END
+ CASE
WHEN CONVERT(VARCHAR(10), MG_VSLVOY_SCHEDULE.LAST_UPDATE_DT, 120) = CONVERT(VARCHAR(10), Getdate() - 1, 120)
AND PORT_CD IN ('ITLIV', 'DEBRV', 'BEZEE', 'GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')
THEN 'C'
ELSE ''
END AS DateStatus,
MG_VSLVOY_HEADER.LEG_CD AS LEG_CD,
PORT_SEQUENCE_NBR AS PORT_SEQUENCE_NBR,
ARRIVAL_SCHEDULE_DT AS ARRIVAL_SCHEDULE_DT,
ARRIVAL_ESTIMATE_DT AS ARRIVAL_ESTIMATE_DT,
ARRIVAL_STATUS_CD AS ARRIVAL_STATUS_CD,
ARRIVAL_ACTUAL_DT AS ARRIVAL_ACTUAL_DT,
DEPART_ESTIMATE_DT AS DEPART_ESTIMATE_DT,
DEPART_ACTUAL_DT AS DEPART_ACTUAL_DT,
DEPART_STATUS_CD AS DEPART_STATUS_CD,

Case When PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'ITLIV' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as ITLIV_E,

Case When PORT_CD = 'DEBRV' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'DEBRV' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'DEBRV' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as DEBRV_E,

Case When PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as BEZEE_E,

Case When PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as GBSOU_E,

Case When PORT_CD = 'MXVER' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'MXVER' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'MXVER' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as MXVER_E,

Case When PORT_CD = 'USGLS' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USGLS' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USGLS' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USGLS_E,

Case When PORT_CD = 'USJAX' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USJAX' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USJAX' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USJAX_E,

Case When PORT_CD = 'USBAL' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USBAL' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USBAL' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USBAL_E,

Case When PORT_CD = 'USCHS' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USCHS' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USCHS' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USCHS_E,

Case When PORT_CD = 'USSSI' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USSSI' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT
when PORT_CD = 'USSSI' and ARRIVAL_STATUS_CD = 'S' THEN ARRIVAL_SCHEDULE_DT end as USSSI_E,

Case When PORT_CD = 'ITLIV' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'ITLIV' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'ITLIV' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as ITLIV_G,

Case When PORT_CD = 'DEBRV' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'DEBRV' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'DEBRV' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as DEBRV_G,

Case When PORT_CD = 'BEZEE' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'BEZEE' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'BEZEE' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as BEZEE_G,

Case When PORT_CD = 'GBSOU' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'GBSOU' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'GBSOU' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as GBSOU_G,

Case When PORT_CD = 'MXVER' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'MXVER' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'MXVER' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as MXVER_G,

Case When PORT_CD = 'USGLS' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USGLS' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USGLS' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USGLS_G,

Case When PORT_CD = 'USJAX' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USJAX' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USJAX' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USJAX_G,

Case When PORT_CD = 'USBAL' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USBAL' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USBAL' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USBAL_G,

Case When PORT_CD = 'USCHS' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USCHS' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USCHS' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USCHS_G,

Case When PORT_CD = 'USSSI' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USSSI' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT
when PORT_CD = 'USSSI' and DEPART_STATUS_CD = 'S' THEN DEPART_SCHEDULE_DT end as USSSI_G



FROM MG_VESSEL_PARTICULAR
INNER JOIN MG_VSLVOY_HEADER
ON MG_VESSEL_PARTICULAR.VESSEL_CD = MG_VSLVOY_HEADER.VESSEL_CD
AND MG_VSLVOY_HEADER.LEG_CD = 'A'
INNER JOIN MG_VSLVOY_PORT_CONTROL
ON MG_VSLVOY_HEADER.VSLVOY_HEADER_ID = MG_VSLVOY_PORT_CONTROL.VSLVOY_HEADER_ID
AND PORT_SEQUENCE_NBR = 1
INNER JOIN MG_VSLVOY_SCHEDULE
ON MG_VSLVOY_PORT_CONTROL.VSLVOY_SCHEDULE_ID = MG_VSLVOY_SCHEDULE.VSLVOY_SCHEDULE_ID
where MG_VESSEL_PARTICULAR.VESSEL_TYPE_CD = 'PCC'
AND MG_VSLVOY_HEADER.SERVICE_CD IN ('EBTAS', 'NCTAS')

AND (PORT_CD IN ('ITLIV', 'DEBRV','MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS') AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT > = DATEADD(MM, 0, GETDATE()) AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE())
OR (PORT_CD = 'USSSI' AND ARRIVAL_STATUS_CD <> 'A' AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT > = DATEADD(MM, 0, GETDATE()) AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE()) OR (PORT_CD IN ('BEZEE', 'GBSOU')
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT > = DATEADD(MM, 0, GETDATE()) AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE()))

AND ARRIVAL_STATUS_CD IN ('E','A','S') AND (ARRIVAL_ESTIMATE_DT IS NOT NULL OR ARRIVAL_ACTUAL_DT IS NOT NULL OR ARRIVAL_SCHEDULE_DT IS NOT NULL)))


gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-16 : 10:20:05
Think about it for a minute. If you GROUP BY (VESSEL_NAME + VOYAGE_NUM) that means that SQL has to aggregate the other columns somehow. That is, there is likely to be more than one row in the result set (before grouping) for each VESSEL_NAME + VOYAGE_NUM. So, if there are two rows, e.g. which one should SQL choose? That's why you need to either aggregate or group by.

BTW GROUP BY VESSEL_NAME, VOYAGE_NUM (no '+') is more natural and easier to read and will work the same.
Go to Top of Page

lahsiv2004
Starting Member

13 Posts

Posted - 2015-02-17 : 03:59:28
quote:
Originally posted by gbritton

Think about it for a minute. If you GROUP BY (VESSEL_NAME + VOYAGE_NUM) that means that SQL has to aggregate the other columns somehow. That is, there is likely to be more than one row in the result set (before grouping) for each VESSEL_NAME + VOYAGE_NUM. So, if there are two rows, e.g. which one should SQL choose? That's why you need to either aggregate or group by.

BTW GROUP BY VESSEL_NAME, VOYAGE_NUM (no '+') is more natural and easier to read and will work the same.



Thanks, you are right. But I still need to group by VESSEL_NAME, VOYAGE_NUM without using MAX for other columns or including other columns in the group by.

Can you please advice how to group by with just 2 columns ?

Thanks.
Go to Top of Page

lahsiv2004
Starting Member

13 Posts

Posted - 2015-02-17 : 03:59:28
quote:
Originally posted by gbritton

Think about it for a minute. If you GROUP BY (VESSEL_NAME + VOYAGE_NUM) that means that SQL has to aggregate the other columns somehow. That is, there is likely to be more than one row in the result set (before grouping) for each VESSEL_NAME + VOYAGE_NUM. So, if there are two rows, e.g. which one should SQL choose? That's why you need to either aggregate or group by.

BTW GROUP BY VESSEL_NAME, VOYAGE_NUM (no '+') is more natural and easier to read and will work the same.



Thanks, you are right. But I still need to group by VESSEL_NAME, VOYAGE_NUM without using MAX for other columns or including other columns in the group by.

Can you please advice how to group by with just 2 columns ?

Thanks.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-17 : 07:21:07
If you Dont aggregate the non grouping columns, which rows should SQL show? There can only be one row per group in the resuots
Go to Top of Page
   

- Advertisement -