|Subject||RE: [firebird-support] Query Help?|
>Hi Ann, Happy Holloween!
> If I understand, the combination ItemID and OrderID is
> unique. If so, this statement:
> > Count(ITEMS.PRODUCTDESCRIPTION) AS CountItems,
> > Sum(ITEMS.ITEMPRICE) AS TotalItems,
> > ITEMS.ORDERID, ITEMS.PRODUCTDESCRIPTION,
> >FROM ITEMS
> >GROUP BY
> > ITEMS.ORDERID,
> > ITEMS.PRODUCTDESCRIPTION,
> > HAVING ITEMS.ORDERID = :OrdID;
> makes no sense whatsoever. If you group by a unique key, there is
> no difference between sum(price) and price. Could you restate the
OrderID is not unique. Its a FK to the Orders (sales) table. So, there may
be one record in the Items table with an OrderID value or there may be a
ItemID is unique only within the context of the OrderID. For each OrderID
in the Orders table, there is guranteed to be a unique sequence of ItemID
for that particular OrderID in the Items table.
Orders Table has a record with an OrderID of 5.
ItemID OrderID Prod Desc
1 5 MyProduct1
2 5 MyProduct2
3 5 MyProduct2
The ItemID field is not meant to represent a unique record in the Items
table as a whole, only to provide a sorting mechanism (ALA Interbase 6.0)
that we could ORDER BY, but alas, FB enforces the SQL standard of forcing
fields used in an ORDER BY to be used in the GROUP BY clause when grouping
is being used as you well know.
I hope this clarifies it. If not not, please let me know, I will be glad to
go into more detail if you need it.
Thanks for your help!