Re: Using GROUP BY and COUNT

Just conjecture, but based on your symptoms, I expect this is due to
the "Warning: Null value is eliminated by an aggregate or other SET
operation" message that SQL Server emits which happens because every
aggregate operator (COUNT, SUM, MIN, MAX) ignores NULLs.  (COUNT(*) is
a special thing).

Not sure how to handle it in FreeTDS or what exactly the interactions
are in the tabular stream and messages, but just throwing that out
there.

If this is indeed the problem, I would expect a workaround in the SQL
using a CASE statement to eliminate the need for any warning will get
you past this for now:

SELECT SUM(CASE WHEN MiddleName IS NOT NULL THEN 1 ELSE 0 END) FROM Person

and

SELECT SUM((CASE WHEN o0_.orderItemId IS NOT NULL THEN 1 ELSE 0 END) AS sclr0
FROM Membership m1_
LEFT JOIN OrderItem o0_ ON m1_.membershipId = o0_.membershipId
GROUP BY m1_.membershipId

Cade
Cade Roux
[email protected]
504-717-4887


On Wed, Dec 19, 2012 at 1:13 PM, Jeremy Livingston
<[email protected]> wrote:
> Also, I have discovered that the LEFT JOIN and GROUP BY clauses aren't
> necessary to reproduce this result. I can trigger the error simply by
> running a COUNT() on a column that contains all null values.
>
> For example: "SELECT COUNT(MiddleName) FROM Person" where MiddleName
> contains all null values will throw the error.
>
> Thanks again for your help.
>
>
> On Wed, Dec 19, 2012 at 1:38 PM, Jeremy Livingston <
> [email protected]> wrote:
>
>> Thank you for the reply.
>>
>> Pardon my ignorance, but I'm not sure how to specify that driver that I'd
>> like to use using bsqlodbc. I get the following error when I attempt my
>> query:
>>
>> "[unixODBC][Driver Manager]Data source name not found, and no default
>> driver specified"
>>
>> Do you have any idea how I can tell it to use a specific driver in
>> odbcinst.ini?
>>
>> Thanks for the help!
>>
>>
>> On Wed, Dec 19, 2012 at 1:21 PM, James K. Lowden <[email protected]>wrote:
>>
>>> On Wed, 19 Dec 2012 09:49:46 -0500
>>> Jeremy Livingston <[email protected]> wrote:
>>>
>>> > I am using FreeTDS v0.91 with unixODBC, PHP 5.3.10, and SQL Server
>>> > 2008. I have my connection to use the 7.2 protocol. I am attempting
>>> > to run the following query:
>>> >
>>> > SELECT COUNT(o0_.orderItemId) AS sclr0
>>> > FROM Membership m1_
>>> > LEFT JOIN OrderItem o0_ ON m1_.membershipId = o0_.membershipId
>>> > GROUP BY m1_.membershipId
>>> >
>>> > This query only throws an error when there is a Membership that has no
>>> > records in the OrderItem table.
>>>
>>> Does your query work with bsqslodbc?  I tried something similar with no
>>> problem:
>>>
>>> $ bsqlodbc -S$S -U$U -P$P <<< 'select count
>>> (c.type) from systypes t left join syscolumns c on t.type = c.type
>>> group by t.type'
>>> ----------
>>>          0
>>>          6
>>>         16
>>>        122
>>>        640
>>>         12
>>>         54
>>>         57
>>>          5
>>>         28
>>>          2
>>>        196
>>>          3
>>>          8
>>>          2
>>>         26
>>>          2
>>>         72
>>>          2
>>>
>>> --jkl
>>> _______________________________________________
>>> FreeTDS mailing list
>>> [email protected]
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>>
>>
>>
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds