Concate two column into one column with sum and groupby

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;

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

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

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;

@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.

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;