Re: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL?

 

On 06.04.2016 o 22:03, 'stwizard' stwizard@att.net [firebird-support] wrote:
> SELECT DISTINCT P.AREA_CODE, P.PHONE_NO,
>
> (SELECT COUNT(*)
>
> FROM PHONE P2
>
> WHERE P2.PHONE_ID = P.PHONE_ID) AS CNT
>
> FROM PHONE P
>
> WHERE P.AREA_CODE IS NOT NULL
>
> GROUP BY 1, 2

[ ... ]

select A, B, (select count(*) from ...)
from ...

is not the same as

select A, B, count(*)
from ...

In the former case, the sub-select is not an aggregate function. It
simply calculates and returns a scalar (a single number), which is
treated just like one more "field" in selected records.
Therefore, for the GROUP BY to work correctly, you'd have to include the
third selected column (sub-select in your case) in the GROUP BY.
But that's not what you wanted, I suppose.
I guess what you really need is a normal grouped query:

select AREA_CODE, PHONE_NO, count(*)
from PHONE
where AREA_CODE is not null
group by 1, 2
having count(*) > 1

And that'll do the job.

Best regards
Tomasz

__._,_.___

---

Posted by: Tomasz Tyrakowski <t.tyrakowski@sol-system.pl>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? The Yahoo Mail app is fast, beautiful and intuitive. Try it today!

---

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___