Re: Count problem...

Sashafay wrote:

> Hi,
>
> I try to solve one problem with count. This problem occurs when I
> getting multi records that I need to count. Take a look on the
> following query:
> *********************************************************
> SELECT INVOICE,COUNT(INVOICE)
> FROM TABLE WHERE ADDR =
> (SELECT ADDR FROM TABLE
> WHERE DELEVERY = 'DD'
> GROUP BY ADDR||POSTDT
> HAVING COUNT(DISTINCT ADDR||POSTDT) > 1)
> GROUP BY INVOICE;
> ********************************************************
> I expected to see each invoice with total count invoices that have
> been delivered to the same address and date. But it's give me only
> invoice and count 1(itself), regardless if two or more invoices have
> been delivered to the same address and the same date. I think, that I
> just lost myself in this query. Hopefully I gave enough information.
>
> Thanks in advance,
> Alex

One problem with your query is your use equals in the WHERE clause for your subselect. Your subselect can return multiple rows so you should use 'IN'. For example:

WHERE addr IN (

   SELECT addr
   FROM ... Daniel Morgan Received on Sun Feb 09 2003 - 11:05:17 CST