![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
![]() |
Wed, May 30 2018 11:19 PM | Permanent Link |
Ian Branch | 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 |
Wed, May 30 2018 11:43 PM | Permanent Link |
Ian Branch | 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 |
Thu, May 31 2018 4:50 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Ian
Just what I had handy SELECT COUNT(_Surname), COUNT(DISTINCT _Surname), COUNT(_Surname) - (SELECT COUNT(DISTINCT _Surname) FROM Contacts) FROM Contacts Roy Lambert |
Thu, May 31 2018 6:00 AM | Permanent Link |
Fernando Dias ![]() | 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] |
Thu, May 31 2018 6:24 AM | Permanent Link |
Ian Branch | 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 |
Thu, May 31 2018 6:35 AM | Permanent Link |
Ian Branch | Forgot to mention - I have stopped on the DBISAM development and am focusing on EDB.
|
Thu, May 31 2018 6:45 AM | Permanent Link |
Fernando Dias ![]() | 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] |
Thu, May 31 2018 7:44 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Fernando
Guilty as charged - I totally missed GROUP BY Roy Lambert |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |