RE: [sqr-users] Count of only one instance

["Bob Stone" <bstone@fastenal.com>]

I agree - if you don't have that 2nd call to the table your 'group by'
statement is grouping too much information, such that the row grouped by
is now unique (count(*) = 1) but only because another column is unique.

select a.col1, b.col2, a.account
from table1 a, table2 b
where b.account = a.account
group by a.col1, b.col2, a.account
having count(*) = 1

This statement doesn't do it, because you're grouping by more than just
account. 

COL1 - COL2 - ACCT
a      b      123
b      d      123

By the group by above each of those 'counts' is singular, even though
there are multiple rows with 123 in table A.

Kevin's statement should work for you.

-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org] On Behalf Of
kevin.reschenberg@sparkpath.com
Sent: Friday, August 25, 2006 11:27 AM
To: This list is for discussion about the SQR database reporting
language from Hyperion Solutions.
Subject: RE: [sqr-users] Count of only one instance

If I'm reading this correctly, you want to pull many columns from
multiple tables and so you can't group on only the account field.  Maybe
this:
 
SELECT ...
FROM   TABLE1 A, TABLE2 B
WHERE  B.ACCOUNT = A.ACCOUNT
AND    A.ACCOUNT IN
       (SELECT ACCOUNT
        FROM   TABLE1
        GROUP BY ACCOUNT
        HAVING COUNT(*) = 1)
 
 
Kevin Reschenberg
SparkPath Technologies, Inc.

sparkpath.com | sqr-info.com
 


 -------- Original Message --------
Subject: [sqr-users] Count of only one instance
From: "Brooke Funk" <Brooke.Funk@firstfedbankkc.com>
Date: Fri, August 25, 2006 9:08 am
To: <sqr-users@sqrug.org>

I am drawing a blank on how to find where only one instance in a table
exists.  There are some accounts that have multiple rows but I am
looking for only those that have only one row in the table.  I have
tried grouping with the count (*) = 1 but it is returning accounts that
have more than 1 row in it.  Can someone help me out?  I can say what I
want but in writing it in SQL it's not working.  I have several other
columns from different tables I am pulling but I want only those
accounts that have only 1 instance in this particular table regardless
of multiple rows in other existing tables I am pulling from.





NOTICE:  This e-mail is intended solely for the use of the
individual to whom it is addressed and may contain information
that is privileged, confidential or otherwise exempt from
disclosure.  If the reader of this e-mail is not the intended
recipient or the employee or agent responsible for delivering
the message to the intended recipient, you are hereby notified
that any dissemination, distribution, or copying of this
communication is strictly prohibited.  If you have received this
communication in error, please immediately notify us by replying
to the original message at the listed e-mail address.

Thank you.


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users