Two Count functions are being multiplied together in join?

Question

I have a table with a Process name and when the process failed plus details. Another table with a Process name and when it was successful plus details.

I am trying to join them so I get the Process, some details, and the total times it failed and completed since July.

The result is multiplying the two COUNT columns together

SELECT e.Process, e.ExceptionType, e.ExceptionReason, COUNT(e.ExceptionType) AS 'Total Exceptions', COUNT(c.Process) AS 'Total Completions'
FROM Errors e
JOIN Completions c
ON c.Process = e.Process
AND '' NOT IN (e.ExceptionType, e.ExceptionReason, e.Process)
AND e.StartedTime > CONVERT(DATETIME, '2019-07-01', 102)
WHERE c.StartedTime > CONVERT(DATETIME, '2019-07-01', 102)
GROUP BY e.Process, e.ExceptionType, e.ExceptionReason
ORDER BY 'Total Exceptions' DESC

It returns what I would expect: The name of the process, the Type of failure, the Reason for failure but then the last two COUNT columns are multiplied together.

If I query these separately for example the first row has 'Total Exceptions' of 844 and 'Total Completions' of 1550

I have tried all JOINs and also tried COUNT(DISTINCT(...)) but then the last two columns return 1 for all rows.

Help?

MORE CONTEXT

select Process, ExceptionType, ExceptionReason, count(ExceptionType)
from Error
where StartedTime > CONVERT(DATETIME, '2019-07-01', 102) and '' NOT IN (Process, ExceptionType, ExceptionReason) and ExceptionType NOT LIKE 'Business%'
group by  ExceptionType, Process, ExceptionReason
order by count(ExceptionType) DESC

Returns a row of:

Process_Name | Exception_Type | Exception_Reason | 844

And then the query:

select Process, count(Process)
from Complete
where CompletedTime > CONVERT(DATETIME, '2019-07-01', 102)
group by Process
order by count(Process) DESC, Process

Returns a row of:

Process_Name | 1550

What I want from my join:

Process_Name | Exception_Type | Exception_Reason | 844 | 1550

What I get:

Process_Name | Exception_Type | Exception_Reason | 1308200 | 1308200

@ravioli No, the Process is a generic term like LogIn or LogOut. It is not a unique ID but rather what type of process was running. Each process would have a unique key to identify which one it was, but that is a separate column. — Virtual Penman, Sep 06 '19 at 17:13
This is exactly what a join is supposed to do, return every matching combination.So if the process #1 has 3 completions and 5 errors then matching errors and completions by process returns15 rows. — Serg, Sep 06 '19 at 17:14
@Serg Thanks for letting me know that is what its supposed to do. Still doesn't help me achieve what I would like to do. — Virtual Penman, Sep 06 '19 at 17:21
@VirtualPenman Sample data from Errors and Completions as well as the expected result set will help. — Neeraj Agarwal, Sep 06 '19 at 17:27
@NeerajAgarwal just added some, good suggestion for more context. — Virtual Penman, Sep 06 '19 at 17:39

score 1 · Accepted Answer · answered Sep 06 '19 at 18:08

1

When you added MORE CONTEXT you used CompletedTime instead of StartedTime. Anyway I suggest the following:

select e.Process, e.ExceptionType, e.ExceptionReason, count(*),
    ccount =
        (
        select count(*)
        from Complete c
        where c.Process = e.Process
        and c.StartedTime = e.StartedTime
        group by c.Process
        )
from Errors e
where e.StartedTime > CONVERT(DATETIME, '2019-07-01', 102)
and '' NOT IN (e.Process, e.ExceptionType, e.ExceptionReason)
and e.ExceptionType NOT LIKE 'Business%'
group by e.ExceptionType, e.Process, e.ExceptionReason
order by count(e.ExceptionType) DESC

answered Sep 06 '19 at 18:08

Neeraj Agarwal

1,059
6
5

This was almost exactly what I needed. I removed `AND c.StartedTime = e.StartedTime` and then it worked! So it seems like the main thing we had to do was to separate one of the COUNT functions into a subquery so that they would not multiply? – Virtual Penman Sep 06 '19 at 18:57
Is this a correlated query? I would love an explanation. – Virtual Penman Sep 06 '19 at 19:04
This seems likely to be a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/a/45252750/3404097) – philipxy Sep 07 '19 at 08:08

ravioli · Answer 2 · 2019-09-06T17:35:43.607

Another way to think about it is to re-write your query with a "seed" table to get all the process names. Then LEFT JOIN to this table any computed values you want to include. Something like this:

SELECT p.ProcessName, e.ExceptionType, e.ExceptionReason, e.'Total Exceptions'
FROM <ProcessTable> p -- Get processes (seed table)
LEFT JOIN (
  SELECT 
    e.Process, 
    e.ExceptionType, 
    e.ExceptionReason, 
    COUNT(e.ExceptionType) AS 'Total Exceptions'
  FROM Errors e
  WHERE '' NOT IN (e.ExceptionType, e.ExceptionReason, e.Process)
  GROUP BY e.Process, e.ExceptionType, e.ExceptionReason
) e ON p.Process = e.Process -- Get process errors
LEFT JOIN (
  SELECT 
    c.Process, 
    COUNT(c.Process) AS 'Total Completions'
  FROM Completions c
  WHERE c.StartedTime > CONVERT(DATETIME, '2019-07-01', 102)
  GROUP BY c.Process
) c ON p.Process = c.Process -- Get process completions
ORDER BY e.'Total Exceptions' DESC

Since you're including ExceptionType and ExceptionReason, this will give you multiple rows per Process if a Process has different ExceptionType / ExceptionReason values. If you only want to count the number of exceptions per process name, then you should exclude these columns from the GROUP BY and the SELECT.

I haven't tested the query so you may need to fix some syntax errors. For example, not sure how you would qualify column names like e.'Total Exceptions'. Hopefully that gets you going.

score 0 · Answer 3 · answered Sep 06 '19 at 17:31

You nee to do the sums separately and then join the results:

SELECT ISNULL(es.Process, cs.Process) AS Process,
       ExceptionType, ExceptionReason, 'Total Exceptions', 'Total Completions'
FROM (
  SELECT e.Process, e.ExceptionType, e.ExceptionReason, COUNT(e.ExceptionType) AS 'Total Exceptions'
  FROM Errors e
  WHERE e.StartedTime > CONVERT(DATETIME, '2019-07-01', 102)
        AND '' NOT IN (e.ExceptionType, e.ExceptionReason, e.Process)
  GROUP BY e.Process, e.ExceptionType, e.ExceptionReason
) AS es FULL JOIN (
  SELECT c.Process, COUNT(*) AS 'Total Completions'
  FROM Completions c
  WHERE c.StartedTime > CONVERT(DATETIME, '2019-07-01', 102)
  GROUP BY c.Process
) AS cs ON cs.Process = es.Process
ORDER BY 'Total Exceptions' DESC

But note that if you have more than one combination of (ExceptionType, ExceptionReason) per process they will be counted separately and completions of the process will be repeated for each combination.

Two Count functions are being multiplied together in join?

3 Answers3