Mark:

Are you sure that your result set will end up with groupings of more than 
one row given all the fields specified in your GROUP BY clause.  In other 
words, have you simplified the statement to ensure that it fails to group 
SOLELY on the date field.  For instance:

  SELECT
    SUM(qtyReserved) AS SumOfReserved,
    COUNT(qtyReserved) AS CountOfReserved,
    dateInvRecv
  FROM
    dbo.tblOrder_InventoryAllocation
  GROUP BY
    dateInvRecv
  ORDER BY
    CountOfReserved DESC

If you haven't done so yet, you might give this a try to ensure the date 
grouping is not working.  Cheers.

- Roger Nedel

  Nedel Software Solutions
  rnedel@b...

==========================

> Li li,
> 
> SELECT     SUM(qtyReserved) AS qtyTtlAloc, positionID, partID, rackID,
> isChildOf, faulty, lotNumber, dateInvRecv
> FROM         dbo.tblOrder_InventoryAllocation
> GROUP BY positionID, partID, rackID, isChildOf, faulty, lotNumber,
> dateInvRecv
> 
> Thanks a lot. You will need to create this table with all these fields,
> among them, only faulty and isChildOf are int, others aree nvarchar. 
> 
> Another clue here is that all other fields grouped fine except the
> dateInvRecv. And this is the only one with the "/"; though I can't 
imaging
> how that could make any difference.
> 
> Li li such a lovely name, are you by any chance Chinese? Sorry if I 
guessed
> wrong.
> 
> Mark
> 
> -----Original Message-----
> From: Li Li [mailto:lilinola@u...]
> Sent: Tuesday, June 12, 2001 3:19 PM
> To: sql language
> Subject: [sql_language] Re: [Re: [problem with grouping]]
> 
> 
> Mark, 
> What is your SQL statement?
> Could you type it here?  I will check it for you from my database 
tonight.
> 
> Li Li
> 
> "Mark.Chen" <Mark.Chen@s...> wrote:
> Li li,
> 
> That is what I thought. So I went into the table and check them. I even 
cut
> and paste from one to another. Did not work.
> 
> Mark
> 
> -----Original Message-----
> From: Li Li [mailto:lilinola@u...]
> Sent: Monday, June 11, 2001 5:09 PM
> To: sql language
> Subject: [sql_language] Re: [problem with grouping]
> 
> 
> I guess the value are actually different even stored date in xx/xx/xxx
> format.
>  The seconds that not display may different.  Check them?
> 
> Li li
> 
> 
> 
> "Mark.Chen" <Mark.Chen@s...> wrote:
> Hi,
> 
> I have encountered the weirdest problem in SQL.
> 
> I have a table with a field to store date in xx/xx/xxxx format. The data
> type is nvarchar.
> 
> I was attempting to GROUP BY this field. Then I found that the result
> separate the records with same date! I tried again with SELECT DISTINCT, 
it
> still does the samething: same date, different records!
> 
> All other fields in this table grouped fine except for this one. This is
> driving me crazy!
> 
> Anyone has a remedy to this?
> 
> Mark
>