SQL SUM query returns all time rather than what is specified in the where clause
I am attempting to gather the time spent from a Spiceworks database. Rather than returning the time spent between specific dates on a specific category, it appears to be returning all the time spent. I have the sql query listed below. It returns 212220 as the time spent on 60 tickets which would be a lot more than what we have actually spent. In the end, I would like to convert the seconds to hours and minutes within the query, but I need to tackle this hurdle first (unless someone has a better way to write the query). Note: Spiceworks uses a SQLite database.
select (SUM(tw.time_spent)) as "Time Spent", count (t.id) as "Number of Tickets", t.category as "Category",t.status as "Status"
FROM ticket_work tw
INNER JOIN tickets t ON t.id = tw.ticket_id
where t.created_at between '2016-05-01 00:00:00' and '2016-05-31 23:59:59' and t.category is 'End User Support'
select (SUM(tw.time_spent)) as "Time Spent", count (t.id) as "Number of Tickets", t.category as "Category",t.status as "Status"
FROM ticket_work tw
INNER JOIN tickets t ON t.id = tw.ticket_id
where t.created_at between '2016-05-01 00:00:00' and '2016-05-31 23:59:59' and t.category is 'End User Support'
ASKER
Where would the GROUP BY statement go in the query?
ASKER
I receive different results when I use the query with the time worked versus not including the time worked and the join statement. The join statement returns 60 tickets. When I just run a select statement on the end user support category I get 101 rows. I have attached a screenshot.Query_Results.pdf
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
For t.id, you say that I should enter a real value. Does it matter which ticket ID I use or can I put any ticket number that was created during that time period?
Yes, 101 ticket rows is possible because some tickets do not have corresponding ticket_work rows.
Could you also post following query results?
SELECT ticket_id, time_spent FROM ticket_work WHERE ticket_id BETWEEN 11950 AND 11977
SELECT ticket_id, time_spent FROM ticket_work WHERE ticket_id BETWEEN 11950 AND 11977
ASKER
24 rows returned in 7ms from: SELECT ticket_id, time_spent FROM ticket_work WHERE ticket_id BETWEEN 11950 AND 11977
ASKER
I should attach a screenshot to the previous post so that you can see the time_spent field.
Yes please
Yes please
I have problems with the internet speed so my answers are delayed but it seems your results are not bad... You should just use DISTINCT in the COUNT.
ASKER
I found out an interesting problem which is Spiceworks related. My guess is the database might be corrupt. I get different results running the same query with the Spiceworks reporting engine. I am going to close this question and check with Spiceworks support to see if they have any clues. I appreciate your response.
You are welcome.
You could also make a db copy and use some SQLite studio to query your data directly. And you should start with all tables reindexing.
You could also make a db copy and use some SQLite studio to query your data directly. And you should start with all tables reindexing.
ASKER
I have it open in SQLite. What is a good way to reindex the tables? That is a great idea. I've done it in SQL 2005 before but this is different.
OK, SQLite is different...
So let suppose the query should have been:
select (SUM(tw.time_spent)) as "Time Spent", count (t.id) as "Number of Tickets", t.category as "Category"
FROM ticket_work tw
INNER JOIN tickets t ON t.id = tw.ticket_id
where t.created_at between '2016-05-01 00:00:00' and '2016-05-31 23:59:59' and t.category is 'End User Support'
Then you have 212220 seconds on 60 tickets which means about 1 hour per ticket. Is it too much?
If yes then you have to show your data sample.
Are you sure time_spent is saved in seconds? What is the data type of this column?