Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Incorrect result using DISTINCT keyword |
Mon, Jun 11 2007 9:03 PM | Permanent Link |
"Al Vas" | Hi,
I have the following statement: select distinct count(Booking_No) as BookingCount from Payroll where Employee_No = 1 There are 4 records, but only 3 unique booking_no's, yet the result I am being given is 4 instead of 3. I have tried this in both application and using DBSYS. Is this maybe a known bug in V3.30 or have I maybe got the syntax wrong? If the former, can anything be done, or is there another way I might be able to approach this? Thanks Alex |
Mon, Jun 11 2007 9:19 PM | Permanent Link |
Steve Forbes Team Elevate | Hi Alex,
You are getting the DISTINCT value of the returned column i.e. COUNT(Booking_No), .. the SQL statement is operating correctly. I think you want: SELECT Booking_No, COUNT(Booking_No) AS BookinCount FROM Payroll WHERE Employee_No = 1 GROUP BY Booking_No; -- Best regards Steve "Al Vas" <forgetit@forgetit.com> wrote in message news:4E78EA8E-9951-4BB8-BFEF-9AC2B403D048@news.elevatesoft.com... > Hi, > > I have the following statement: > > select distinct count(Booking_No) as BookingCount from Payroll where > Employee_No = 1 > > There are 4 records, but only 3 unique booking_no's, yet the result I am > being given is 4 instead of 3. I have tried this in both application and > using DBSYS. Is this maybe a known bug in V3.30 or have I maybe got the > syntax wrong? > > If the former, can anything be done, or is there another way I might be > able to approach this? > > Thanks > > Alex > |
Mon, Jun 11 2007 9:25 PM | Permanent Link |
"Robert" | "Al Vas" <forgetit@forgetit.com> wrote in message news:4E78EA8E-9951-4BB8-BFEF-9AC2B403D048@news.elevatesoft.com... > Hi, > > I have the following statement: > > select distinct count(Booking_No) as BookingCount from Payroll where > Employee_No = 1 > Your SQL does not look right (I might be missing what you're trying to do) If yo are just trying to get the unique Booking_No s, then why use a "count"? Simply do a select unique booking_no, and you'll get them. A unique count would with your data yield two rows (one with a 1, one with a 2) though I'm not sure what you wold use that for. But if that's what you want, you have to group by booking_no. Robert > There are 4 records, but only 3 unique booking_no's, yet the result I am > being given is 4 instead of 3. I have tried this in both application and > using DBSYS. Is this maybe a known bug in V3.30 or have I maybe got the > syntax wrong? > > If the former, can anything be done, or is there another way I might be > able to approach this? > > Thanks > > Alex > |
Tue, Jun 12 2007 5:09 AM | Permanent Link |
"Al Vas" | Thanks for your help guys. I am working out someone elses code in this
instance thus the confusion, although I am known to create confusing SQL statements all on my own on many occasions. Thanks again "Al Vas" <forgetit@forgetit.com> wrote in message news:4E78EA8E-9951-4BB8-BFEF-9AC2B403D048@news.elevatesoft.com... > Hi, > > I have the following statement: > > select distinct count(Booking_No) as BookingCount from Payroll where > Employee_No = 1 > > There are 4 records, but only 3 unique booking_no's, yet the result I am > being given is 4 instead of 3. I have tried this in both application and > using DBSYS. Is this maybe a known bug in V3.30 or have I maybe got the > syntax wrong? > > If the former, can anything be done, or is there another way I might be > able to approach this? > > Thanks > > Alex > |
Tue, Jun 12 2007 5:37 AM | Permanent Link |
"Al Vas" | Hi Steve,
The solution you gave is not quite what I am trying to achieve. Sorry I didn't really explain the situation too well. What we are doing is charging a fee per booking (shift). A shift can be split into two and will contain the same booking_No. The booking fee is per shift, not split shift. So what I am after is one record with a column called BookingCount containing the grand total count of the number of unique bookings in the table. The aim is to have a single record resultant set with a column called BookingCount with the value 3 in it. There are 4 bookings but only 3 unique bookings as defined by unique booking numbers in the example. RecNo Booking_no 1 1 2 1 3 2 4 3 resultant set RecNo BookingCount 1 3 Cheers Alex "Steve Forbes" <ozmosys@spamfreeoptusnet.com.au> wrote in message news:07828F63-91C1-46A5-9F93-8F151270D22B@news.elevatesoft.com... > Hi Alex, > > You are getting the DISTINCT value of the returned column i.e. > COUNT(Booking_No), .. the SQL statement is operating correctly. I think > you want: > > SELECT Booking_No, COUNT(Booking_No) AS BookinCount FROM Payroll WHERE > Employee_No = 1 GROUP BY Booking_No; > > -- > Best regards > > Steve > > "Al Vas" <forgetit@forgetit.com> wrote in message > news:4E78EA8E-9951-4BB8-BFEF-9AC2B403D048@news.elevatesoft.com... >> Hi, >> >> I have the following statement: >> >> select distinct count(Booking_No) as BookingCount from Payroll where >> Employee_No = 1 >> >> There are 4 records, but only 3 unique booking_no's, yet the result I am >> being given is 4 instead of 3. I have tried this in both application and >> using DBSYS. Is this maybe a known bug in V3.30 or have I maybe got the >> syntax wrong? >> >> If the former, can anything be done, or is there another way I might be >> able to approach this? >> >> Thanks >> >> Alex >> > > |
Tue, Jun 12 2007 11:11 AM | Permanent Link |
Steve Forbes Team Elevate | Hi Alex,
The correct syntax for your query is SELECT COUNT(DISTINCT Booking_No) FROM Payroll however, this does not work in DBISAM V4 (or 3) You could use an intermediate memory table containing the result set from SELECT DISTINCT Booking_No FROM Payroll and invoke a query on it .. Good luck -- Best regards Steve "Al Vas" <noreply@noreply.com> wrote in message news:4842944C-FBFC-4FCA-AA26-7A40C56360C5@news.elevatesoft.com... > Hi Steve, > > The solution you gave is not quite what I am trying to achieve. Sorry I > didn't really explain the situation too well. > > What we are doing is charging a fee per booking (shift). A shift can be > split into two and will contain the same booking_No. The booking fee is > per shift, not split shift. So what I am after is one record with a > column called BookingCount containing the grand total count of the number > of unique bookings in the table. The aim is to have a single record > resultant set with a column called BookingCount with the value 3 in it. > There are 4 bookings but only 3 unique bookings as defined by unique > booking numbers in the example. > > RecNo Booking_no > > 1 1 > 2 1 > 3 2 > 4 3 > > resultant set > > RecNo BookingCount > > 1 3 > > Cheers > Alex > > "Steve Forbes" <ozmosys@spamfreeoptusnet.com.au> wrote in message > news:07828F63-91C1-46A5-9F93-8F151270D22B@news.elevatesoft.com... >> Hi Alex, >> >> You are getting the DISTINCT value of the returned column i.e. >> COUNT(Booking_No), .. the SQL statement is operating correctly. I think >> you want: >> >> SELECT Booking_No, COUNT(Booking_No) AS BookinCount FROM Payroll WHERE >> Employee_No = 1 GROUP BY Booking_No; >> >> -- >> Best regards >> >> Steve >> >> "Al Vas" <forgetit@forgetit.com> wrote in message >> news:4E78EA8E-9951-4BB8-BFEF-9AC2B403D048@news.elevatesoft.com... >>> Hi, >>> >>> I have the following statement: >>> >>> select distinct count(Booking_No) as BookingCount from Payroll where >>> Employee_No = 1 >>> >>> There are 4 records, but only 3 unique booking_no's, yet the result I am >>> being given is 4 instead of 3. I have tried this in both application >>> and using DBSYS. Is this maybe a known bug in V3.30 or have I maybe got >>> the syntax wrong? >>> >>> If the former, can anything be done, or is there another way I might be >>> able to approach this? >>> >>> Thanks >>> >>> Alex >>> >> >> > > |
Tue, Jun 12 2007 8:00 PM | Permanent Link |
"Al Vas" | Thanks Steve,
Always accurate as usual. Have a great day Alex "Steve Forbes" <ozmosys@spamfreeoptusnet.com.au> wrote in message news:87915C00-3FB9-43B4-A888-D676FA61CF16@news.elevatesoft.com... > Hi Alex, > > The correct syntax for your query is > > SELECT COUNT(DISTINCT Booking_No) FROM Payroll > > however, this does not work in DBISAM V4 (or 3) > > You could use an intermediate memory table containing the result set from > > SELECT DISTINCT Booking_No FROM Payroll > > and invoke a query on it .. > > Good luck > > -- > Best regards > > Steve > > "Al Vas" <noreply@noreply.com> wrote in message > news:4842944C-FBFC-4FCA-AA26-7A40C56360C5@news.elevatesoft.com... >> Hi Steve, >> >> The solution you gave is not quite what I am trying to achieve. Sorry I >> didn't really explain the situation too well. >> >> What we are doing is charging a fee per booking (shift). A shift can be >> split into two and will contain the same booking_No. The booking fee is >> per shift, not split shift. So what I am after is one record with a >> column called BookingCount containing the grand total count of the number >> of unique bookings in the table. The aim is to have a single record >> resultant set with a column called BookingCount with the value 3 in it. >> There are 4 bookings but only 3 unique bookings as defined by unique >> booking numbers in the example. >> >> RecNo Booking_no >> >> 1 1 >> 2 1 >> 3 2 >> 4 3 >> >> resultant set >> >> RecNo BookingCount >> >> 1 3 >> >> Cheers >> Alex >> >> "Steve Forbes" <ozmosys@spamfreeoptusnet.com.au> wrote in message >> news:07828F63-91C1-46A5-9F93-8F151270D22B@news.elevatesoft.com... >>> Hi Alex, >>> >>> You are getting the DISTINCT value of the returned column i.e. >>> COUNT(Booking_No), .. the SQL statement is operating correctly. I think >>> you want: >>> >>> SELECT Booking_No, COUNT(Booking_No) AS BookinCount FROM Payroll WHERE >>> Employee_No = 1 GROUP BY Booking_No; >>> >>> -- >>> Best regards >>> >>> Steve >>> >>> "Al Vas" <forgetit@forgetit.com> wrote in message >>> news:4E78EA8E-9951-4BB8-BFEF-9AC2B403D048@news.elevatesoft.com... >>>> Hi, >>>> >>>> I have the following statement: >>>> >>>> select distinct count(Booking_No) as BookingCount from Payroll where >>>> Employee_No = 1 >>>> >>>> There are 4 records, but only 3 unique booking_no's, yet the result I >>>> am being given is 4 instead of 3. I have tried this in both >>>> application and using DBSYS. Is this maybe a known bug in V3.30 or >>>> have I maybe got the syntax wrong? >>>> >>>> If the former, can anything be done, or is there another way I might be >>>> able to approach this? >>>> >>>> Thanks >>>> >>>> Alex >>>> >>> >>> >> >> > > |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |