Re: need aid with this query

Hello Ed,

The problem with your query is that it does not work:

SQL> create table t1(CustomerNumber int, CustomerBranch int);

Table created.

SQL> insert into t1 values(123, NULL);

1 row created.

SQL> insert into t1 values(123, 1);

1 row created.

SQL> insert into t1 values(123, 2);

1 row created.

SQL> insert into t1 values(221, NULL);

1 row created.

SQL> insert into t1 values(221, 5);

1 row created.

SQL> insert into t1 values(555, 1);

1 row created.

SQL> insert into t1 values(555, 9);

1 row created.

SQL> insert into t1 values(125, NULL
  2
SQL> select customernumber
  2 from
  3 (select customernumber, count(*) cnt_all , count(customerbranch)   4 cnt_branch
  5 from t1 ) va
  6 where va.cnt_all = va.cnt_branch ;
(select customernumber, count(*) cnt_all , count(customerbranch)

        *
ERROR at line 3:
ORA-00937: not a single-group group function

SQL> VC
"Ed prochak" <ed.prochak_at_magicinterface.com> wrote in message news:4b5394b2.0401271100.120070aa_at_posting.google.com... > "VC" <boston103_at_hotmail.com> wrote in message  news:<K9kRb.158353$xy6.770548_at_attbi_s02>...
> > "soni29" <soni29_at_hotmail.com> wrote in message
> > news:cad7a075.0401261754.651428da_at_posting.google.com...
> > > hi,
> > > i need some help with a query, also to find out if this is even
> > > possible with sql. currently i have a table with the following data:
> > >
> > > CustomerNumber CustomerBranch
> > > 123 NULL
> > > 123 1
> > > 123 2
> > > 221 NULL
> > > 221 5
> > > 555 1
> > > 555 9
> > > 125 NULL
> > >
> > > now in this data, CustomerNumber and CustomerBranch are the keys, each
> > > customer MUST have a CustomerBranch with null, those NULL
> > > CustomerBranch's represent the company headquaters, while the ones
> > > with numbers are the other offices. Occassionally data is missing, in
> > > the example above CustomerNumber 555 does not have a NULL
> > > CustomerBranch, this is wrong. Is there anyway in SQL to find all
> > > those CustomerNumbers who do not have a null, there can only be one
> > > null per CustomerNumber. i was thinking about using a
> > > count(CustomerBranch) but not sure how to write it to count all those
> > > CustomerBranchs per CustomerNumber that are equal to 0, if that's the
> > > right way to do it.
> > >
> > > Thank you.

>
> and VC wrote, (I moved the top post down for context)
>

> >

> >

> >

> >

> >
> >

> >
>
> I've always disliked distinct in that it implies you did not have the
> full search criteria. If nothing else, in most queries you can change
> DISTINCT to a COUNT(*).
> I'd solve this case something like this (uses an inline view):
>
> select customernumber
> from
> (select customernumber, count(*) cnt_all , count(customerbranch)
> cnt_branch
> from t1 ) va
> where va.cnt_all = va.cnt_branch ;
>
> This makes use of the fact that count() does not count NULL values. So
> if there is no main branch, then there is no record for that customer
> where the branch is NULL.
>
>  HTH,
> ed