Count Distinct values from multiple JOINS MS SQL 2016

Question

I seek to count the output of DISTINCT values from a query that joins 3 tables.

Here is the query (MS SQL 2016 STD) I am running with so far:

SELECT
    f.factor_desc,
    pf.value,
    p.measure_date
FROM
    gagestation.dbo.part p
INNER JOIN gagestation.dbo.part_factor pf ON
    p.part_id = pf.part_id
INNER JOIN gagestation.dbo.qcc_file qf ON
    p.qcc_file_id = qf.qcc_file_id
INNER JOIN gagestation.dbo.factor f ON
    pf.factor_id = f.factor_id
WHERE
    factor_desc = 'Work Order #'
Order BY pf.value

This query outputs records like this:

Work Order # / Date last updated

Work Order #    26724/1 2021-06-22 10:57:35
Work Order #    26724/1 2021-06-23 04:51:47
Work Order #    26724/1 2021-06-23 13:19:42
Work Order #    26724/1 2021-06-24 04:36:43
Work Order #    26724/1 2021-06-24 07:49:51
Work Order #    26724/1 2021-06-24 09:49:48
Work Order #    26724/1 2021-06-24 12:48:58
Work Order #    26724/1 2021-06-25 05:30:01

Looking to get this to look like:

Workorder# / TotalCount 

26724/1 / 11
34553/5 / 2
31556/2 / 5

It has 2 columns one with the workorder# and the other with the total count of rows for that workorder by counting the occurances of its number in the pf.value column.

I have tried to add Count/Distict in various configurations and all end up failing in variations of this:

is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

I am looking to take this info and push it to another database via a linked server and I know how to do counts for distinct values but everything I have tried has ended in an error.

If you want to take "this info", and push it to another database, then... what is the problem? Maybe you should show some data, and desired output, and tell why current output does not match desired output. Also tell1 about the errors that ended everything (and what where you doing then?) — Luuk, Jun 24 '22 at 17:26
`COUNT(DISTINCT yourvalue)` maybe? Really unclear what you want here. Sample data and expected results would help — Charlieface, Jun 24 '22 at 18:36
[mre] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) PS See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify one non-sole non-poster commenter `x` re a comment. Posters, sole commenters & followers of posts always get notified. But without `@` others get no notification. — philipxy, Jun 25 '22 at 01:13
[Error related to only_full_group_by when executing a query in MySql](https://stackoverflow.com/q/34115174/3404097) This is an easily found faq. Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, Jun 25 '22 at 02:43

Paul Maxwell · Answer 1 · 2022-06-25T06:10:38.707

To introduce use of the COUNT() function you need to also use a GROUP BY clause, so that the count is performed for each unique combination of values within f.factor_desc and pf.value. Inside the count function you can then use DISTINCT to qualify how the count is performed.

SELECT
      f.factor_desc
    , pf.value
    , count(distinct p.measure_date) as TotalCount 
FROM gagestation.dbo.part p
    INNER JOIN gagestation.dbo.part_factor pf ON p.part_id = pf.part_id
    INNER JOIN gagestation.dbo.qcc_file qf ON p.qcc_file_id = qf.qcc_file_id
    INNER JOIN gagestation.dbo.factor f ON pf.factor_id = f.factor_id
WHERE f.factor_desc = 'Work Order #'
GROUP BY
      f.factor_desc
    , pf.value
ORDER BY
      value

nb: You can remove the column f.factor_desc from both the select and group by clauses if you don't want that column in the final result.

For this specific query it might not be necessary to use all 4 tables - but without data to test with this is merely a possibility.

Count Distinct values from multiple JOINS MS SQL 2016

1 Answers1