From: boston103@hotmail.com
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" wrote in message
news:4b5394b2.0401271100.120070aa@posting.google.com...
> "VC" wrote in message
news:...
> > "soni29" wrote in message
> > news:cad7a075.0401261754.651428da@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)
>
> > Hello,
> >
> > SQL-92:
> >
> > select distinct customernumber
> > from t1
> > where customernumber not in (select customernumber from t1 where
> > customerbranch is null)
> >
> > SQL-99 (probably faster):
> >
> > select distinct customernumber from
> > (select customernumber, first_value(customerbranch) over (partition by
> > customernumber order by customerbranch desc) first
> > from t1)
> > where first is not null
> >
> >
> > VC
> >
>
> 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
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
