473,395 Members | 1,386 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

group by

Hi.

I have read lots, but obviously not the right stuff.
A reference pointer would be fine, or an answer. I in fact got an oracle
solution that I am still researching, but was wondering about either a generic
or an ms sql server solution?
I'd like to use sql to find out:

given a metadata table
where object is a table name
attribute is a col name,
keyseq is if the attribute is
part of a primary key

AND

given (by rules, not db design) that you should only have a single attribute
with a given non null value in keyseq

AND

table metatable(object varchar2, attribute varchar2, keyseq int)
table1, col1, 1
table1, col2, 2
table1, col3, null
table1, col4, null
table2, col1, 1
table2, col2, 1
table2, col3, null
table2, col4, null
table3, col1, 1
table3, col2, 2
table3, col3, null
table3, col4, null
I'd like to easily find out what tables have more than one attribute for any
keyseq that is not null, and which attribute it was with the problem.
Is it possible?

I started off with select object, attribute, keyseq from metatable
where keyseq is not null, and got a few thousand rows.

Then I tried a count(*) and group by (I wish I knew this aggregate stuff
better) like this:

select object, keyseq, count(keyseq) from metatable
where keyseq is not null
group by object, keycolseq
order by (count(keyseq))

and got a few thousand rows, where the very last row told me the object that
had more than one attribute with a single keyseq value.

So yes, I can figure it out, but I was hoping/wondering if there was a better
way, using just sql, that would return what object/attribute had the
'violation'.

I thought I might be able to use the above query in some sort of
subquery/exists combination, but I'm at a loss.

Some helpful person (thanks m cadot) in the Oracle group gave me some
guidance and made an initial suggestion which I show here, and a more advanced
solution I'm not showing because I don't understand it, and it might be oracle
specific.

select object, keyseq, count(keyseq)
from metatable
where keyseq is not null
group by object, keyseq
having count(*) > 1 ---<----- just this line to add
order by count(keyseq)

thanks for your time and knowledge.

Thanks
Jeff Kish
Jeff Kish
Jun 19 '06 #1
7 2071
This should work. WHERE limits which cases go into the tally.
HAVING limits which results are output after the tally is completed.

In this case, it limits the output to those object-keyseq combinations
where the count is greater than one, which I believe is what you're
after.
Some helpful person (thanks m cadot) in the Oracle group gave me some
guidance and made an initial suggestion which I show here, and a more advanced
solution I'm not showing because I don't understand it, and it might be oracle
specific.

select object, keyseq, count(keyseq)
from metatable
where keyseq is not null
group by object, keyseq
having count(*) > 1 ---<----- just this line to add
order by count(keyseq)


Jun 19 '06 #2
Jeff Kish (je*******@mro.com) writes:
Some helpful person (thanks m cadot) in the Oracle group gave me some
guidance and made an initial suggestion which I show here, and a more
advanced solution I'm not showing because I don't understand it, and it
might be oracle specific.

select object, keyseq, count(keyseq)
from metatable
where keyseq is not null
group by object, keyseq
having count(*) > 1 ---<----- just this line to add
order by count(keyseq)


It can't be any more standard SQL than this. The above SELECT should
run on any RDBMS that supports SQL.

HAVING is like WHERE, but is applied after the GROUP BY, and thus
permits filters with aggregate functions.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 19 '06 #3
On Mon, 19 Jun 2006 21:58:38 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.se> wrote:
Jeff Kish (je*******@mro.com) writes:
Some helpful person (thanks m cadot) in the Oracle group gave me some
guidance and made an initial suggestion which I show here, and a more
advanced solution I'm not showing because I don't understand it, and it
might be oracle specific.

select object, keyseq, count(keyseq)
from metatable
where keyseq is not null
group by object, keyseq
having count(*) > 1 ---<----- just this line to add
order by count(keyseq)


It can't be any more standard SQL than this. The above SELECT should
run on any RDBMS that supports SQL.

HAVING is like WHERE, but is applied after the GROUP BY, and thus
permits filters with aggregate functions.

How would I add the attribute column so I know exactly which ones are
of interest? That was what is the most difficult thing for me to
figure out.

Thanks
Jun 20 '06 #4
Jeff Kish (ki*******@charter.net) writes:
How would I add the attribute column so I know exactly which ones are
of interest? That was what is the most difficult thing for me to
figure out.


Not sure that I understood your original question, but try:

SELECT a.object, a.attribute, a.keyseq
FROM metatable a
JOIN (SELECT object, keyseq
FROM metatable
WHERE keyseq IS NOT NULL
GROUP BY object, keyseq
HAVING COUNT(*) > 1) AS b ON a.object = b.object
AND a.keyseq = b.keyseq

The thing in parenthesis is a derived table. You could think of a
derived table as a temp table within the query, but never materialised.
Or even computed, the optimizer may recasts the computation order
to get performance. This is a great tool to write complex queries
effeciently.

And, yes, this is ANSI-SQL that should run on most RDBMS. (It's not
as vintage as HAVING though.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 20 '06 #5
On Tue, 20 Jun 2006 22:04:41 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.se> wrote:
Jeff Kish (ki*******@charter.net) writes:
How would I add the attribute column so I know exactly which ones are
of interest? That was what is the most difficult thing for me to
figure out.


Not sure that I understood your original question, but try:

SELECT a.object, a.attribute, a.keyseq
FROM metatable a
JOIN (SELECT object, keyseq
FROM metatable
WHERE keyseq IS NOT NULL
GROUP BY object, keyseq
HAVING COUNT(*) > 1) AS b ON a.object = b.object
AND a.keyseq = b.keyseq

The thing in parenthesis is a derived table. You could think of a
derived table as a temp table within the query, but never materialised.
Or even computed, the optimizer may recasts the computation order
to get performance. This is a great tool to write complex queries
effeciently.

And, yes, this is ANSI-SQL that should run on most RDBMS. (It's not
as vintage as HAVING though.)

Wow.
I'll have to study up some more on the 'ON' and 'AS' syntax.

Thanks so much Erland.

I appreciate both the answer and the education.
kind regards
Jun 21 '06 #6
Jeff Kish (ki*******@charter.net) writes:
Wow.
I'll have to study up some more on the 'ON' and 'AS' syntax.


The AS is optional and is only for defining an alias for the derived
table. The alias is always mandatory, though.

The ON is part of the newer ANSI syntax for joins which was introduced to
handle left joins, but once you have get used to it, you will use it for
all you joins, because it makes the queries so much clearer.


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 21 '06 #7
On Wed, 21 Jun 2006 06:56:24 +0000 (UTC), Erland Sommarskog
<es****@sommarskog.se> wrote:
Jeff Kish (ki*******@charter.net) writes:
Wow.
I'll have to study up some more on the 'ON' and 'AS' syntax.


The AS is optional and is only for defining an alias for the derived
table. The alias is always mandatory, though.

The ON is part of the newer ANSI syntax for joins which was introduced to
handle left joins, but once you have get used to it, you will use it for
all you joins, because it makes the queries so much clearer.

study study study.. it never ends...
Thanks again. Bonus that it works for sql server 2000 as well, I imagine, the
newer version.
Regards
Jeff Kish
Jun 22 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Mike | last post by:
I am sure that I am making a simple boneheaded mistake and I would appreciate your help in spotting in. I have just installed apache_2.0.53-win32-x86-no_ssl.exe php-5.0.3-Win32.zip...
2
by: Tom Loach | last post by:
Our system administrator set up an NT server group in order to allow our users to login to our application via https to our sql server. The group appears as a User in SQL Server when you look at...
4
by: Chad Richardson | last post by:
I've always been mistified why you can't use a column alias in the group by clause (i.e. you have to re-iterate the entire expression in the group by clause after having already done it once in the...
2
by: BillD | last post by:
I'm trying to derive a schema from a base schema. I want to redefine a "group" from the base schema in my derived schema in order to add more options to the "choice" aggregate (see schema1.xsd...
16
by: michael | last post by:
Is it possible to get all href URLs contained in a unordered list and place them in an array? Or in fact two different arrays, differently named one for each <ul> group? <ul> <li><a...
7
by: Darin | last post by:
I have a report that sub-totals on a group, then grand-totals at the report footer. If there's only one group, the sub-total and grand total are redundant, so I only want to show one of them. I...
1
by: David Horowitz | last post by:
Hi folks. I need to create a report that has a Group Header that pulls certain data from the Detail section. It's something like this: +--Report---------------------------------------- |...
7
by: Sameh Ahmed | last post by:
Hello there IsInrole gives ya the means to check if the current or impersonated user belongs to a specific windows role or group. is there a way to do the same without using ADSI to check if...
2
by: jon|k | last post by:
hi all-- i need to do a transformation that removes duplicates (among other things). to accomplish that, i'm trying to use for-each-group, but it doesn't work. i need to select for duplicates by...
3
by: Sebastian | last post by:
Hello all I have a report where I have two nested groups. I know there are only three standard options for running sum: None, Over Group and Over All. I have a MyTextBox in detail section where...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.