How to make multiple aggregate function without break / divided the fields

Is it possible to make an aggregate function without break / divided the grouping fields? I make a query but it will divided into duplicate value in the first field, here is my query:

SELECT      TOP 5 empname AS 'EMP Name',    
            SUM (CASE WHEN prod = 'P' THEN 1 ELSE 0 END) AS 'Count of Prod',
            COUNT (prod) AS 'Total Account',
            FORMAT (COALESCE (SUM (CASE WHEN prod = 'P' THEN 1 ELSE 0 END) / COUNT (prod), 0), 'P') AS '% Prod',
            DATEDIFF(DAY, t_start, t_end) as 'Duration Trip'
FROM        Sampletable
WHERE       empname NOT IN ('NA') AND 
            empname IS NOT NULL AND
            t_end IS NOT NULL
GROUP BY    empname, 
            prod,
            t_end,
            t_start
ORDER BY    [Count of Prod] DESC

My expected result:

Emp. Name	Count of Prod	Total Account	% Prod	Duration Trip
Emp.1	62	63	98,41%	30
Emp.2	45	48	93,75%	28
Emp.3	20	22	90,91%	25
Emp.4	20	24	83,33%	22
Emp.5	15	19	78,95%	20

Thank you in advance.

Answer

If you want one row per empname, then that should be the only column in the group by (or perhaps other columns that describe each employee without multiplying the number of rows).

That suggests something like this:

SELECT TOP 5 empname,    
       SUM(CASE WHEN prod = 'P' THEN 1 ELSE 0 END) AS prod_count,
       COUNT prod) AS Total_Account,
       FORMAT(AVG(CASE WHEN prod = 'P' THEN 1.0 ELSE 0 END), '%P') AS prod_ratio,
       SUM(DATEDIFF(DAY, t_start, t_end)) as trip_duration
FROM Sampletable
WHERE empname NOT IN ('NA') AND 
      empname IS NOT NULL AND
      t_end IS NOT NULL
GROUP BY empname
ORDER BY prod_count DESC;

Note some of the changes to the query:

• The column aliases are simplified so no escape characters are needed.
• The GROUP BY has only empname.
• The logic for the proportion of products is simplified using AVG().
• I assume that the trip duration should be a sum of the individual durations.
• I did not remove it, but empname IS NOT NULL is redundant, because the NOT IN handles this.