Group By magic anyone?

Hi folks,
I know that the standard is to provide a lot of DDL and DML to make clear
what is being asked...but here's is a very straighforward occurence in
business data and I wondered if there was a feature of Transact-SQL that mad
e
it unnecessary to use any inner queries/subqueries etc...
Below is the result of a straighforward Group By on both columns Branch_OU
and Sort_Code (order by Branch_Ou) both are varchar (10) but this shouldn't
matter
Branch_OU, Sort_code
A, a
A, a
A, a
B, b
B, b1
B, b
C, c
C, c
C, c
D, d
D, d1
D, d2
my Question
Is it possible to use any feature of the language to know which groups of
Branch_Ous have homogeneous occurences of Sort codes (in this case A and C)
and another query to know which don't (B, D), or is this only possible with
subqueries
Regards and thanks in advance
CharlesAHomogeneous:
select distinct
Branch_OU
, Sort_code
from
MyTable
group by
Branch_OU
, Sort_code
having
min (Sort_Code) = max (Sort Code)
Non-homogeneous:
select distinct
Branch_OU
, Sort_code
from
MyTable
group by
Branch_OU
, Sort_code
having
min (Sort_Code) < max (Sort Code)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"CharlesA" <CharlesA@.discussions.microsoft.com> wrote in message
news:0FB90A2C-336A-435A-A14B-2AE6C399B813@.microsoft.com...
Hi folks,
I know that the standard is to provide a lot of DDL and DML to make clear
what is being asked...but here's is a very straighforward occurence in
business data and I wondered if there was a feature of Transact-SQL that
made
it unnecessary to use any inner queries/subqueries etc...
Below is the result of a straighforward Group By on both columns Branch_OU
and Sort_Code (order by Branch_Ou) both are varchar (10) but this shouldn't
matter
Branch_OU, Sort_code
A, a
A, a
A, a
B, b
B, b1
B, b
C, c
C, c
C, c
D, d
D, d1
D, d2
my Question
Is it possible to use any feature of the language to know which groups of
Branch_Ous have homogeneous occurences of Sort codes (in this case A and C)
and another query to know which don't (B, D), or is this only possible with
subqueries
Regards and thanks in advance
CharlesA|||Correction:
select distinct
Branch_OU
from
MyTable
group by
Branch_OU
having
min (Sort_Code) = max (Sort Code)
Non-homogeneous:
select
Branch_OU
from
MyTable
group by
Branch_OU
having
min (Sort_Code) < max (Sort Code)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eG40qPwNGHA.3408@.TK2MSFTNGP14.phx.gbl...
Homogeneous:
select distinct
Branch_OU
, Sort_code
from
MyTable
group by
Branch_OU
, Sort_code
having
min (Sort_Code) = max (Sort Code)
Non-homogeneous:
select distinct
Branch_OU
, Sort_code
from
MyTable
group by
Branch_OU
, Sort_code
having
min (Sort_Code) < max (Sort Code)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"CharlesA" <CharlesA@.discussions.microsoft.com> wrote in message
news:0FB90A2C-336A-435A-A14B-2AE6C399B813@.microsoft.com...
Hi folks,
I know that the standard is to provide a lot of DDL and DML to make clear
what is being asked...but here's is a very straighforward occurence in
business data and I wondered if there was a feature of Transact-SQL that
made
it unnecessary to use any inner queries/subqueries etc...
Below is the result of a straighforward Group By on both columns Branch_OU
and Sort_Code (order by Branch_Ou) both are varchar (10) but this shouldn't
matter
Branch_OU, Sort_code
A, a
A, a
A, a
B, b
B, b1
B, b
C, c
C, c
C, c
D, d
D, d1
D, d2
my Question
Is it possible to use any feature of the language to know which groups of
Branch_Ous have homogeneous occurences of Sort codes (in this case A and C)
and another query to know which don't (B, D), or is this only possible with
subqueries
Regards and thanks in advance
CharlesA|||CharlesA wrote:
> Hi folks,
> I know that the standard is to provide a lot of DDL and DML to make clear
> what is being asked...but here's is a very straighforward occurence in
> business data and I wondered if there was a feature of Transact-SQL that m
ade
> it unnecessary to use any inner queries/subqueries etc...
> Below is the result of a straighforward Group By on both columns Branch_OU
> and Sort_Code (order by Branch_Ou) both are varchar (10) but this shouldn'
t
> matter
> Branch_OU, Sort_code
> A, a
> A, a
> A, a
> B, b
> B, b1
> B, b
> C, c
> C, c
> C, c
> D, d
> D, d1
> D, d2
> my Question
> Is it possible to use any feature of the language to know which groups of
> Branch_Ous have homogeneous occurences of Sort codes (in this case A and C
)
> and another query to know which don't (B, D), or is this only possible wit
h
> subqueries
> Regards and thanks in advance
> CharlesA
For a "straightforward" question this certainly raises a few questions
in my mind. What is/are the key(s) of your table(s)? Your sample data
is a bit confusing. You say that it's the result of a GROUP BY on both
columns and yet it appears to contain duplicates, at least in my
browser. That isn't possible unless you also grouped by some other
column(s) you didn't tell us about. What do you mean by "know which
groups [...]"? You mean you want to select only those groups? Or
exclude them? Or display them differently in some way? DDL, sample data
and expected results would have helped us a lot.
In the absense of a better spec I'll speculate that this example might
help:
SELECT branch_ou, MIN(sort_code) AS sort_code
FROM your_table
GROUP BY branch_ou
HAVING MIN(sort_code)=MAX(sort_code);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I really need a coffee...
Homogeneous is:
select
Branch_OU
from
MyTable
group by
Branch_OU
having
min (Sort_Code) = max (Sort Code)
Non-homogeneous is OK.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:e3MnjSwNGHA.3284@.TK2MSFTNGP14.phx.gbl...
Correction:
select distinct
Branch_OU
from
MyTable
group by
Branch_OU
having
min (Sort_Code) = max (Sort Code)
Non-homogeneous:
select
Branch_OU
from
MyTable
group by
Branch_OU
having
min (Sort_Code) < max (Sort Code)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eG40qPwNGHA.3408@.TK2MSFTNGP14.phx.gbl...
Homogeneous:
select distinct
Branch_OU
, Sort_code
from
MyTable
group by
Branch_OU
, Sort_code
having
min (Sort_Code) = max (Sort Code)
Non-homogeneous:
select distinct
Branch_OU
, Sort_code
from
MyTable
group by
Branch_OU
, Sort_code
having
min (Sort_Code) < max (Sort Code)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"CharlesA" <CharlesA@.discussions.microsoft.com> wrote in message
news:0FB90A2C-336A-435A-A14B-2AE6C399B813@.microsoft.com...
Hi folks,
I know that the standard is to provide a lot of DDL and DML to make clear
what is being asked...but here's is a very straighforward occurence in
business data and I wondered if there was a feature of Transact-SQL that
made
it unnecessary to use any inner queries/subqueries etc...
Below is the result of a straighforward Group By on both columns Branch_OU
and Sort_Code (order by Branch_Ou) both are varchar (10) but this shouldn't
matter
Branch_OU, Sort_code
A, a
A, a
A, a
B, b
B, b1
B, b
C, c
C, c
C, c
D, d
D, d1
D, d2
my Question
Is it possible to use any feature of the language to know which groups of
Branch_Ous have homogeneous occurences of Sort codes (in this case A and C)
and another query to know which don't (B, D), or is this only possible with
subqueries
Regards and thanks in advance
CharlesA|||Thanks Tom,
I was going to write back to say , "these are not working, I'll explain
myself better", but you stepped in twice to fix your own code...that's very
decent of you...
your 2nd post's heterogeneous works great as does your third's homogeneous,
and the solution is superb.
David, thanks for your thoughts...you are indeed 100% correct, there is a
mistake in the way I framed the question and the data as I sent it (with
duplicates across the rows...i.e it could not have been in a Group by, that
was quite muddle-headed of me)
don't worry about the keys in this instance, I was just basically saying
"this happens a lot" is there a Group By non-subquery way of determining
which groups have homogeneous vs non-homogeneous subgroups
I appreciate all posts
thanks again and regards
CharlesA|||Hey, when your caffeine level is down a quart, things happen. ;-) Glad it
worked out.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"CharlesA" <CharlesA@.discussions.microsoft.com> wrote in message
news:4085F9D6-319D-41EA-B597-829CDB1218F4@.microsoft.com...
Thanks Tom,
I was going to write back to say , "these are not working, I'll explain
myself better", but you stepped in twice to fix your own code...that's very
decent of you...
your 2nd post's heterogeneous works great as does your third's homogeneous,
and the solution is superb.
David, thanks for your thoughts...you are indeed 100% correct, there is a
mistake in the way I framed the question and the data as I sent it (with
duplicates across the rows...i.e it could not have been in a Group by, that
was quite muddle-headed of me)
don't worry about the keys in this instance, I was just basically saying
"this happens a lot" is there a Group By non-subquery way of determining
which groups have homogeneous vs non-homogeneous subgroups
I appreciate all posts
thanks again and regards
CharlesA|||Good for you. When caffeine levels are down over here, nothing happens.
Nothing at all. :)
ML
http://milambda.blogspot.com/|||>...I was just basically saying
>"this happens a lot" is there a Group By non-subquery way of determining
>which groups have homogeneous vs non-homogeneous subgroups
SELECT Branch_OU, COUNT(distinct Sort_code) as OneOrMore
FROM MyTable
GROUP BY Branch_OU
COUNT(distinct Sort_code) will show how many different values of
Sort_code there are for each Branch_OU. This can be tested in a
HAVING clause.
HAVING COUNT(distinct Sort_code) > 1
Note that NULLs will not be counted, so a mix of one value and NULLs
will count as 1.
Roy|||Thanks Roy,
that solution is superb too, and it was exactly the kind of thing I had in
mind when I said, "how can I do this without any subqueries!"
Always best to use a built-in feature if you can, they tend to be highly
optimised
Thanks again and regards,
CharlesA