473,320 Members | 1,936 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,320 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 2070
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: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.