CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Belliez
Belliez

Reputation: 5376

SQL Count total unique values

This is an improved question based on what I asked yesterday and decided pivot tables is too complicated for what I need. I have the following code which shows me how many tickets have been completed by each user.

From the advice from a similar post yesterday (https://stackoverflow.com/users/2291321/ydaetskcor) suggested to simplify it to work as follows:

SELECT Names, COUNT(Names)
FROM Table1
GROUP BY Names

Despite trying to get my code below to play ball I am still struggling to work out how I can get the code below to display a count of each user in the list.

The code below displays a long list of users but for some reason I cannot get it to display a count of unique users (and I know this is a relatively simple sql!):

DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;

-- Date format: YYYY-MM-DD
SET @StartDate = '2013-11-06 00:00:00'
SET @EndDate = GETDATE()  -- Today


SELECT  (select Username from Membership where UserId =  Ticket.CompletedBy) as TicketStatusChangedBy

FROM         Ticket INNER JOIN
                      TicketStatus ON Ticket.TicketStatusID = TicketStatus.TicketStatusID INNER JOIN
                      Membership ON Ticket.CheckedInBy = Membership.UserId
WHERE TicketStatus.TicketStatusName = 'Completed' and Ticket.ClosedDate >= @StartDate --(GETDATE() - 1)
and Ticket.ClosedDate <= @EndDate --(GETDATE()-0)
ORDER BY Ticket.CompletedBy ASC, Ticket.ClosedDate ASC

Displays the following

Paul
Matt
Matt
Bob
Matt
Paul
Matt
Matt
...  (could be many different users)

Ideally I am looking to display the number of times a user is in the list i.e.

Paul  |  2
Matt  |  5
Bob   |  1

Upvotes: 0

Views: 125

Answers (2)

Lamak
Lamak

Reputation: 70638

You just need to refactor your query a little. I also recommend that you join again with the table Membership instead of using a correlated subquery:

DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;

-- Date format: YYYY-MM-DD
SET @StartDate = '2013-11-06 00:00:00'
SET @EndDate = GETDATE()  -- Today


SELECT  M2.Username AS TicketStatusChangedBy,
        COUNT(*) N
FROM Ticket T
INNER JOIN TicketStatus TS
    ON T.TicketStatusID = TS.TicketStatusID 
INNER JOIN Membership M
    ON T.CheckedInBy = M.UserId
LEFT JOIN Membership M2
    ON T.CompletedBy = M2.UserId
WHERE TS.TicketStatusName = 'Completed' 
AND T.ClosedDate >= @StartDate --(GETDATE() - 1)
and T.ClosedDate <= @EndDate --(GETDATE()-0)
GROUP BY M2.Username
ORDER BY M2.Username

Upvotes: 0

gzaxx
gzaxx

Reputation: 17590

I changed your query a bit. You were joining Membership but not by CompletedBy column but I did not see any reason to do so. I'm counting by Ticket.Id but I'm not sure such column exists there, so you may need to change that.

DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;

-- Date format: YYYY-MM-DD
SET @StartDate = '2013-11-06 00:00:00'
SET @EndDate = GETDATE()  -- Today


SELECT Username, Count(Ticket.Id) as Count
FROM  Ticket
INNER JOIN TicketStatus ON Ticket.TicketStatusID = TicketStatus.TicketStatusID
INNER JOIN Membership ON Ticket.CompletedBy = Membership.UserId
WHERE TicketStatus.TicketStatusName = 'Completed' and Ticket.ClosedDate >= @StartDate
      and Ticket.ClosedDate <= @EndDate
GROUP BY Username

Upvotes: 1

Related Questions