group by and non-aggregate columns

How do I return non-aggregate, non-group columns in a group by select? For
example:
select count(*) numstores, b.reshipcd from branches b join reship r on
b.reshipcd = r.reshipcd group by b.reshipcd
works fine but
select count(*) numstores, r.addr, b.reshipcd from branches b join reship r
on b.reshipcd = r.reshipcd group by b.reshipcd
fails since r.addr is neither in the group by clause nor an aggregate
column. Yet I need the address in the select. How can this be done?
Thanks for any help.
Bernie YaegerAnything that's non-aggregate needs to be in the GROUP BY clause.
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
news:U_bub.22583$Uj2.3600687@.news4.srv.hcvlny.cv.net...
> How do I return non-aggregate, non-group columns in a group by select?
For
> example:
> select count(*) numstores, b.reshipcd from branches b join reship r on
> b.reshipcd = r.reshipcd group by b.reshipcd
> works fine but
> select count(*) numstores, r.addr, b.reshipcd from branches b join reship
r
> on b.reshipcd = r.reshipcd group by b.reshipcd
> fails since r.addr is neither in the group by clause nor an aggregate
> column. Yet I need the address in the select. How can this be done?
> Thanks for any help.
> Bernie Yaeger
>|||Hi Adam,
Tx for the response - of course, that's only logical.
Thanks again,
Bernie
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:u6DELrVrDHA.2520@.TK2MSFTNGP09.phx.gbl...
> Anything that's non-aggregate needs to be in the GROUP BY clause.
>
> "Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message
> news:U_bub.22583$Uj2.3600687@.news4.srv.hcvlny.cv.net...
> > How do I return non-aggregate, non-group columns in a group by select?
> For
> > example:
> >
> > select count(*) numstores, b.reshipcd from branches b join reship r on
> > b.reshipcd = r.reshipcd group by b.reshipcd
> >
> > works fine but
> >
> > select count(*) numstores, r.addr, b.reshipcd from branches b join
reship
> r
> > on b.reshipcd = r.reshipcd group by b.reshipcd
> >
> > fails since r.addr is neither in the group by clause nor an aggregate
> > column. Yet I need the address in the select. How can this be done?
> >
> > Thanks for any help.
> >
> > Bernie Yaeger
> >
> >
>|||You can create a sub-query which will do the Group By part, then join that result to the Outer quer
Select...
From <table> t
( select count(*) numstores, b.reshipcd from branches b join reship r o
b.reshipcd = r.reshipcd group by b.reshipc
) As
Where t.Columnname = g.Columnnam|||Hi Prasanna,
I was thinking about something like this, but it's only logical if the other
columns are all the same, thus making the group by logical if it contains
them all.
Thanks for your response.
Bernie
"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:58FB235A-8AFF-4F3A-87A0-AF8A28C5365B@.microsoft.com...
> You can create a sub-query which will do the Group By part, then join that
result to the Outer query
> Select...
> From <table> t,
> ( select count(*) numstores, b.reshipcd from branches b join
reship r on
> b.reshipcd = r.reshipcd group by b.reshipcd
> ) As g
> Where t.Columnname = g.Columnname
>