Concate two column into one column with sum and groupby

Question

I have a table Tour Below

TourDate	EmployeeName	DeparturePlace	ArrivalPlace	DayAllowance
20/09/2024	Mr. Devid	Dhaka	Khulna	0
20/09/2024	Mr. Devid	Khulna	Dhaka	1
20/09/2024	Mr. Devid	Rangpur	Dhaka	1
20/09/2024	Mr. Devid	Dhaka	Rangpur	0
22/09/2024	Mr. Sam	Rajshahi	Rangapur	0
22/09/2024	Mr. Sam	Rangpur	Rajshahi	1

I want to concate two column into one column which TourDate is same Sum DayAllowance and GroupBy Date Like below:

TourDate	Name	VisitedPlace	DayAllowance
20/09/2024	Mr. Devid	From Dhaka To Khulna, Khulna To Dhaka, Rangpur To Dhaka and Dhaka to Rangpur	2
22/09/2024	Mr. Sam	From Rajshahi To Rangpur and Rangpur To Rajshahi	1

TourDate and Name is Grouped when Same VisitedPlace Concate two column DeparturePlace and ArrivalPlace. DayAllowance is the Sum of DayAllowance column of Tour table. How can i Create that table?

Jignesh Kumar · Answer

You can remove your time using Cast to date, SELECT CAST(TourDate AS DATE) as date , EmployeeName AS Name, STRING_AGG(CONCAT('From ', DeparturePlace, ' To ', ArrivalPlace), ', ') WITHIN GROUP (ORDER BY TourDate) AS VisitedPlace, SUM(DayAllowance) AS DayAllowance FROM Tour GROUP BY CAST(TourDate AS DATE), EmployeeName ORDER BY CAST(TourDate AS DATE), EmployeeName;

Naimish Makwana · Answer

Try this. SELECT CONVERT(VARCHAR, TourDate, 23) AS TourDate, EmployeeName AS Name, STRING_AGG(CONCAT('From ', DeparturePlace, ' To ', ArrivalPlace), ', ') AS VisitedPlace, SUM(DayAllowance) AS DayAllowance FROM Tour GROUP BY CONVERT(VARCHAR, TourDate, 23), EmployeeName;

Naimish Makwana · Answer

Try this SELECT DATE(TourDate) AS TourDate, EmployeeName AS Name, GROUP_CONCAT(CONCAT('From ', DeparturePlace, ' To ', ArrivalPlace) SEPARATOR ', ') AS VisitedPlace, SUM(DayAllowance) AS DayAllowance FROM Tour GROUP BY DATE(TourDate), EmployeeName; Thanks

Debashish Mahonta · Answer

I got an error in your script. 'DATE' is not a recognized built-in function name.

Jignesh Kumar · Answer

You can try this one, SELECT DATE(TourDate) AS TourDate, -- Extract only the date part EmployeeName AS Name, STRING_AGG(CONCAT('From ', DeparturePlace, ' To ', ArrivalPlace), ', ') WITHIN GROUP (ORDER BY TourDate) AS VisitedPlace, SUM(DayAllowance) AS DayAllowance FROM Tour -- YourTableName GROUP BY DATE(TourDate), EmployeeName -- Group by only the date part ORDER BY TourDate, EmployeeName;

Debashish Mahonta · Answer

@Jignesh Kumar Thank You for your reply. My TourDate column is DateTime together Like 20/09/2024 12:21:00 PM. Your Script Group By Date and Time also. I want Group By Date not with Time.

Jignesh Kumar · Answer

Hello, You can use below script to get desire result, SELECT TourDate, EmployeeName AS Name, STRING_AGG(CONCAT('From ', DeparturePlace, ' To ', ArrivalPlace), ', ') WITHIN GROUP (ORDER BY TourDate) AS VisitedPlace, SUM(DayAllowance) AS DayAllowance FROM Tour -- YourTableName GROUP BY TourDate, EmployeeName ORDER BY TourDate, EmployeeName;