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:-
realationship
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
tonelm54Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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.
tonelm54Author Commented:
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?
tonelm54Author Commented:
Ive put it on SQLFiddle, can someone correct me where Im goinf wrong?

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

Thank you
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
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...)
tonelm54Author Commented:
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'):-
Resultset
PatHartmanCommented:
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.
PortletPaulEE Topic AdvisorCommented:
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
PatHartmanCommented:
Paul,
That syntax does not work with Jet/ACE which is why I didn't mention it.
PortletPaulEE Topic AdvisorCommented:
please accept my apologies

seems Access does not allow for COUNT(DISTINCT ...)
but the logic still applies

What you are seeking is a count of the distinct values, but you are getting a count of all non-null values

see: http://sqlfiddle.com/#!5/fe2ac/14
|       Branch |                 User | Count(DISTINCT `FaultLog`.`User`) | Sum(`FaultLogNotes`.`Fix Time`) |
|--------------|----------------------|-----------------------------------|---------------------------------|
|  Rider Books |   Gilberte Axelrod   |                                 1 |                              10 |
|  Rider Books |   Lyndsey Metcalfe   |                                 1 |                             135 |
|  Rider Books |     Rosaria Steere   |                                 1 |                             356 |
|  Rider Books |   Shandi Boudreaux   |                                 1 |                             346 |
|     Time Out |     Candie Horvath   |                                 1 |                            1262 |
|     Time Out |    Christia Roache   |                                 1 |                             928 |
|     Time Out |    Kellee Valentin   |                                 1 |                             251 |
|     Time Out | Leatha Middlebrook   |                                 1 |                             157 |
|     Time Out |        Shante Lipe   |                                 1 |                             531 |
|    Vermilion |    Clotilde Doiron   |                                 1 |                             830 |
|    Vermilion |     Dorian Drexler   |                                 1 |                             174 |
|    Vermilion |      Jerri Myricks   |                                 1 |                             620 |
|    Vermilion |      Nena Humiston   |                                 1 |                             315 |
|    Vermilion |      Vania Knutsen   |                                 1 |                             463 |
| Virgin Books | Jacquelin Fujiwara   |                                 1 |                             802 |
| Virgin Books |    Luis Wawrzyniak   |                                 1 |                             183 |
| Virgin Books |  Yajaira Bachelder   |                                 1 |                             743 |

Open in new window

SELECT
    `FaultLog`.`Branch`
   , `FaultLog`.`User`
   , Count(DISTINCT `FaultLog`.`User`)
   , Sum(`FaultLogNotes`.`Fix Time`)
FROM `FaultLog`
INNER JOIN `FaultLogNotes` ON `FaultLog`.`Fault Number` = `FaultLogNotes`.`FaultLogNo`
GROUP BY
     `FaultLog`.`Branch`
   , `FaultLog`.`User`
;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.