SELECT
  tt.analyst_id,
  mu.userFullName,
  tt.status_id,
  ms.description,
  COUNT(tt.ticket_no)
FROM
  dbHelpdesk.dbo.t_ticket AS tt LEFT JOIN
  dbHelpdesk.dbo.m_status AS ms ON tt.status_id = ms.id LEFT JOIN
  webuser.dbo.m_User AS mu ON tt.analyst_id = mu.userName
WHERE
  tt.dispatcher_id IN ('ISS', 'JWT', 'FMJ', 'NSE', 'BTO', 'BCA')
  AND tt.request_date >= '2017-01-01'
GROUP BY
  tt.analyst_id,
  mu.userFullName,
  tt.status_id,
  ms.description
ORDER BY
  mu.userFullName

DECLARE @list TABLE ( Analyst VARCHAR(50),
[Wait for Dispatch] TINYINT,
[On Progress] TINYINT,
[Pending] TINYINT,
[Complete] TINYINT,
[Waiting Revision] TINYINT,
[Closed] TINYINT,
[Reject] TINYINT,
[Waiting Confirmation] TINYINT,
[Reopen] TINYINT
)
INSERT INTO @list
        (Analyst,
         [Wait for Dispatch],
         [On Progress],
         Pending,
         Complete,
         [Waiting Revision],
         Closed,
         Reject,
         [Waiting Confirmation],
         Reopen)
SELECT
  mu.userFullName,
  --ms.description,
  CASE 
    WHEN ms.description = 'Wait for Dispatch' THEN COUNT(tt.ticket_no)
    ELSE 0
  END AS [Wait for Dispatch],
  CASE 
    WHEN ms.description = 'On Progress' THEN COUNT(tt.ticket_no)
    ELSE 0
  END AS [On Progress],
  CASE 
    WHEN ms.description = 'Pending' THEN COUNT(tt.ticket_no)
    ELSE 0
  END AS Pending,
  CASE 
    WHEN ms.description = 'Complete' THEN COUNT(tt.ticket_no)
    ELSE 0
  END AS Complete,
  CASE 
    WHEN ms.description = 'Waiting Revision' THEN COUNT(tt.ticket_no)
    ELSE 0
  END AS [Waiting Revision],
  CASE 
    WHEN ms.description = 'Closed' THEN COUNT(tt.ticket_no)
    ELSE 0
  END AS Closed,
  CASE 
    WHEN ms.description = 'Reject' THEN COUNT(tt.ticket_no)
    ELSE 0
  END AS Reject,
  CASE 
    WHEN ms.description = 'Waiting Confirmation' THEN COUNT(tt.ticket_no)
    ELSE 0
  END AS [Waiting Confirmation],
  CASE 
    WHEN ms.description = 'Reopen' THEN COUNT(tt.ticket_no)
    ELSE 0
  END AS Reopen
FROM
  dbHelpdesk.dbo.t_ticket AS tt LEFT JOIN
  dbHelpdesk.dbo.m_status AS ms ON tt.status_id = ms.id LEFT JOIN
  webuser.dbo.m_User AS mu ON tt.analyst_id = mu.userName
WHERE
  tt.dispatcher_id IN ('ISS', 'JWT', 'FMJ', 'NSE', 'BTO', 'BCA')
  AND tt.request_date >= '2017-01-01'
GROUP BY
  mu.userFullName,
  ms.description
ORDER BY
  mu.userFullName

--SELECT
--  Analyst,
--  SUM([Wait for Dispatch]) AS [Wait for Dispatch],
--  SUM([On Progress]) AS [On Progress],
--  SUM(Pending) AS Pending,
--  SUM(Complete) AS Complete,
--  SUM([Waiting Revision]) AS [Waiting Revision],
--  SUM(Closed) AS Closed,
--  SUM(Reject) AS Reject,
--  SUM([Waiting Confirmation]) AS [Waiting Confirmation],
--  SUM(Reopen) AS Reopen,
--  SUM([Wait for Dispatch])+SUM([On Progress])+SUM(Pending)+SUM(Complete)+SUM([Waiting Revision])+SUM(Closed)+SUM(Reject)+SUM([Waiting Confirmation])+SUM(Reopen) AS Total
--FROM
--	@list
--GROUP BY
--  Analyst
  
SELECT
	*
FROM
	(
    SELECT
      Analyst,
      SUM([Wait for Dispatch]) AS [Wait for Dispatch],
      SUM([On Progress]) AS [On Progress],
      SUM(Pending) AS Pending,
      SUM(Complete) AS Complete,
      SUM([Waiting Revision]) AS [Waiting Revision],
      SUM(Closed) AS Closed,
      SUM(Reject) AS Reject,
      SUM([Waiting Confirmation]) AS [Waiting Confirmation],
      SUM(Reopen) AS Reopen,
      SUM([Wait for Dispatch])+SUM([On Progress])+SUM(Pending)+SUM(Complete)+SUM([Waiting Revision])+SUM(Closed)+SUM(Reject)+SUM([Waiting Confirmation])+SUM(Reopen) AS Total
    FROM
	    @list
    GROUP BY
      Analyst
  ) AS x
ORDER BY
  x.Total DESC,
  x.Analyst