CODEWITHSUNDEEP
sql-server
David Brierton
David Brierton

Reputation: 7397

Using Count in SQL Server

I am trying to run a query in SQL Server to loop through the associates and count how many transactions they processed.

select 
    Id, submitter_name, submitter_id,
    count(*) as numberOfTickets
from
    Tickets 
where 
    created_at between '2018-11-13' and '2018-11-14' 
    and ',' + tags + ',' like '%,' + 'tickets' + ',%'
group by 
    Id, submitter_name, submitter_id;

This results shows the associates name over and over with one transaction. I am trying to count and show there name once with the total count for numberOfTickets.

I am not sure where I am going wrong here. Any help would be greatly appreciated!

Upvotes: 0

Views: 57

Answers (1)

dfundako
dfundako

Reputation: 8324

I'm going to make the assumption that Id is the Id of the ticket. You don't want that included in your select and groupby because that is the number you want to count.

select submitter_name, submitter_id,
       count(*) as numberOfTickets
from  Tickets 
where created_at between '2018-11-13' and '2018-11-14' and
      ',' + tags + ',' like '%,' + 'tickets' + ',%'
group by submitter_name, submitter_id;

As a side note, for the love of all that is holy, don't store your tags like that. Your RDBMS will hate you for it. Use a many-to-many relationship.

Upvotes: 1

Related Questions