Using Count in SQL Server

Question

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!

Can you please supply some sample data and expected results? Also, your WHERE clause ',' + tags + ',' like '%,' + 'tickets' + ',%' very strongly suggests you're storing delimited data in your table. This is a really bad idea; I would strongly suggest redesigning your database to a more normalised format. (Or at least using a string splitter, and not LIKE with a leading wild card, but a redesign would be a far better idea). — Thom A
– Thom A ♦, Commented Jan 22, 2019 at 18:39

dfundako · Accepted Answer · 2019-01-22 18:39:05Z

1

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.

answered Jan 22, 2019 at 18:39

dfundako

8,3643 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Count in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related