I have a query that looks like this:

SELECT
B.Company,
COUNT(*) AS QTY
FROM Table1 A
INNER JOIN Table2 B
ON A.CompanySk = B.EDWCompanySK
WHERE A.StartedDate >= '20171128'
GROUP BY B.Company
ORDER BY QTY DESC 

However, there is a field name "Ticket" in table1 that has duplicate values, that I need to exclude. How can I return the count with excluding duplicate "ticket" values.

  • Can table1.Ticket be NULL? – Denis Rubashkin Dec 4 at 13:09
  • There are zero NULL values in Table1.Ticket – Ethan Heidtman Dec 4 at 13:11
  • Use COUNT(DISTINCT Table1.Ticket) – Akina Dec 4 at 13:13

Use the next query

SELECT
B.Company,
COUNT(DISTINCT A.Ticket) AS QTY
FROM Table1 A
INNER JOIN Table2 B
ON A.CompanySk = B.EDWCompanySK
WHERE A.StartedDate >= '20171128'
GROUP BY B.Company
ORDER BY QTY DESC
  • It is returning the error: "The multi-part identifier Table1.Ticket could not be bound." – Ethan Heidtman Dec 4 at 13:20
  • I've corrected my answer – Denis Rubashkin Dec 4 at 13:22
  • That worked, thank you! – Ethan Heidtman Dec 4 at 13:27

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.