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

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
>

"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
>

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
>

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
>>
>
>

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
>>>
>>
>>
>
>

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
>>>>
>>>
>>>
>>
>>
>
>