1

I am trying to figure how to query in MSSQL. What I am trying to query is how many tickets each employee submitted (in the database the reference is submitter_id in the dbo.Tickets table) In this column is the associates number. I have another table called dbo.Users that has an id which will match the submitter_id from the Tickets table. I am trying to figure how I can create a count to see how many tickets were submitted in a certain date range (showing the users name and the count). Any help with this would be greatly appreciated. (I cant figure out how to do the count* part)

SELECT Tickets.submitter_id, Users.Name, Tickets.created_at
FROM Tickets
INNER JOIN Users ON Tickets.submitter_id=Users.id;

Also tried:

select
    u.Id,
    u.Name,
    t.created_at,
    t.submitter_id,
    COUNT(*) as numberOfTickets
from Users as u
join Tickets as t on t.submitter_id = u.Id
group by u.Id, u.Name, t.submitter_id
where t.created_at between '2017/11/01' and '2018/08/23'
David Brierton
  • 6,977
  • 12
  • 47
  • 104
  • 1
    Check out `JOIN` and `GROUP BY` – Veljko89 Aug 23 '18 at 14:37
  • Per previous comments JOIN and GROUP BY is something to check out and if you are expecting the records between certain date range then your WHERE clause should also specify the date range. E.g. BETWEEN clause or may be some operators like <=, >= for the date range. Check out this post in case you need any help with the dates https://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates – KRM Aug 23 '18 at 14:56

1 Answers1

2

EDITED: Added HAVING clause

As was sad in the comments you should use JOIN and GROUP. Here is the example of solution:

select
u.Id,
u.Name,
t.submitter_id,
COUNT(*) as numberOfTickets
from Users as u
join Tickets as t on t.submitter_id = u.Id and t.date between '2017/11/01' and '2018/11/01'
group by u.Id, u.Name, t.submitter_id
Kiryl Khadanovich
  • 420
  • 7
  • 10