asked on

Query count and sum not working

Good evening,
Im trying to write a simple query which counts all the tickets open by each user, and the sums all the time spent.

SELECT [Fault Logs].Branch, [Fault Logs].User, Count([Fault Logs].User) AS CountOfUser, Sum([Fault Logs: Notes].[Fix Time]) AS [SumOfFix Time]
FROM [Fault Logs] INNER JOIN [Fault Logs: Notes] ON [Fault Logs].[Fault Number] = [Fault Logs: Notes].FaultLogNo
WHERE ((([Fault Logs: Notes].[Date and time])>=#1/1/2015# And ([Fault Logs: Notes].[Date and time])<=#8/31/2015#))
GROUP BY [Fault Logs].Branch, [Fault Logs].User;

Open in new window

What I don't understand is when I run the query, the count record is returning the wrong count. I cant figure out what its counting, but its not counting the number of tickets open by the user.

The relationships in my tables are:-

Im using Microsoft access, and Ill try and get some test data together if possible, but the current database is 700Mb at the moment, so a bit big :-S

Can anyone easily see the problem easily?

Thanks in advance

PatHartman

Count(...) counts the domain, not the distinct values in the domain. So you are getting a count of Fault Logs: Notes rather than Fault Logs.

Sam, 12
Sam, 3
Joe, 1
Joe, 4
Joe, 5

Results in
Sam, 2, 15
Joe, 3, 10

PS -
1. Names should not include spaces or special characters or duplicate a reserved word.
2. Your relationships are not correct. Relationships are defined between the primary key of one table and a foreign key in the other. Also, if you are not going to enforce RI, defining a relationship does nothing.

tonelm54

ASKER

I still don't understand as the count is returning a number which I cant find, its not the total number of rows, not the number of notes per ticket.

Unfortantly I inherited this database, it is something im working on, and im in the process changing from Access to MySQL but it is VERY large and in constant use so is taking a long time. So hence the spaces in field names :-(

The relationship is there cause the notes table contains the 'date and time field' which I need for the where clause. I don't understand your comment, as surely I still need it for the where clause?

tonelm54

ASKER

Ive put it on SQLFiddle, can someone correct me where Im goinf wrong?

http://sqlfiddle.com/#!5/fe2ac/3

Thank you

Jeffrey Coachman

It may be easier for experts to troubleshoot if you just posted a sample of the database and the exact expected results.

Without seeing your data we are all guessing what the "correct" values should be.
(Perhaps you have nulls, perhaps you should convert the nulls to zeros, ...or perhaps not...)

tonelm54

ASKER

For the test data I've uploaded (cause there are millions or rows) it seems to only have 1 fault per user per branch, so it should display 1 count (from the 'FaultLog'):-

PatHartman

The count in the query on SQL Fiddle is correct. Shandi Boudreaux has 3 records in the Notes table and THAT is what is being counted. In a 1-many join, it is the MANY side rows that are counted.

I'm not sure what you think you are counting. The users are unique so the count would never be other than 1. But SQL counts the rows in the domain that is being aggregated. Three rows for Shandi, 6 rows for Candie, etc.

PortletPaul

SELECT
[Fault Logs].Branch
, [Fault Logs].User
, Count(DISTINCT [Fault Logs].User) AS CountOfUser
, Count([Fault Logs].User) AS CountOfUserNotes
, Sum([Fault Logs: Notes].[Fix Time]) AS [SumOfFix Time]
FROM [Fault Logs]
INNER JOIN [Fault Logs: Notes] ON [Fault Logs].[Fault Number] = [Fault Logs: Notes].FaultLogNo
WHERE ((([Fault Logs: Notes].[Date and time]) >= #1 / 1 / 2015 # AND ([Fault Logs: Notes].[Date and time]) <= #8 / 31 / 2015 #))
GROUP BY
[Fault Logs].Branch
, [Fault Logs].User

COUNT(...) by default will simply increment by one for EVERY non-NULL value it encounters

COUNT(DISTINCT ....) will only increment by one if the column's value is different from any previously encountered value in that column

TestTable
TestCol
NULL
Fred
Betty
Barney
Wilma
Fred

select COUNT(TestCol) from TestTable
expected result = 5

select COUNT(DISTINCT TestCol) from TestTable
expected result = 4

select COUNT(*) from TestTable
expected result = 6

PatHartman

Paul,
That syntax does not work with Jet/ACE which is why I didn't mention it.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial