Hi Team,
You may have seen this query in the DBISAM forum.  It didn't work because of the 'distinct'.

"select JobTickets.jobtype as "Job Type", count(JobTickets.MSN) as "Total Units", count(distinct JobTickets.MSN) as "Unique Units", count(JobTickets.msn)-count(distinct JobTickets.msn) as "Repeats"
from JobTickets
where (trim(BOTH ' ' from JobTickets.msn) <> '') and (JobTickets.Date_in >= :StartDate) and (JobTickets.date_in <= :EndDate) and (jobtickets.buscode = :buscode)
group by JobType"

No Roy I haven't formatted it.  It is here for reference only.

I scrapped moving to DBISAM and went to EDB.
The query works perfectly.

Ian

Spoke too soon.

In my test DB and script the following section doesn't work.

"count(JobTickets.MSN) - count(distinct JobTickets.MSN) as "Repeats""

The left and right work OK on their own but the subtraction doesn't.

On their own, the counts produce 2 & 1 respectively and therefore the above should produce 1 for "Repeats" but, nope, just 0.

No error are produced.

Sigh!

Ian

Ian

Just what I had handy



SELECT
COUNT(_Surname),
COUNT(DISTINCT _Surname),
COUNT(_Surname) - (SELECT COUNT(DISTINCT _Surname) FROM Contacts)

FROM Contacts

Roy Lambert

Ian,

Roy's solution is a good workaround if there was no GROUP BY, I believe that you will have some issues adapting it to your case after adding the GROUP clause. Here is an alternative workaround:

SELECT
  JobType as "Job Type",
  TotalUnits AS "Total Units",
  UniqueUnits AS "Unique Units",
  (TotalUnits - UniqueUnits) AS "Repeats"
FROM
 (SELECT
    JobType,
    COUNT(MSN) AS TotalUnits,
    COUNT(DISTINCT MSN) as UniqueUnits
  FROM
    JobTickets
  WHERE
    (TRIM(BOTH ' ' from msn) <> '') and
    (Date_in >= :StartDate) and
    (Date_in <= :EndDate) and
    (buscode = :buscode)
   GROUP BY
     JobType
 ) AS _tmpTable


I have also posted the solution for DBISAM, duno if you have seen it.

--
Fernando Dias
[Team Elevate]

Hi Fernando,
Thanks for that.  I tried something similar but obviously not quite right.

My thanks to both you and Roy for your support.
Regards,
Ian

Forgot to mention - I have stopped on the DBISAM development and am focusing on EDB.

Ian,

I would do the same.
Both are great products, I have applications using both, but EDB is the future, and a powerful beast
 
--
Fernando Dias
[Team Elevate]

Fernando


Guilty as charged - I totally missed GROUP BY


Roy Lambert