sorry, typo. I didn't look back to see who wrote what, just remembered it.
Dave
-Original Message-
From: Deanna Schneider [mailto:[EMAIL PROTECTED]
Sent: Friday, August 26, 2005 1:14 PM
To: CF-Talk
Subject: Re: SQL Count Question...
Who you talking about "he." I'
Who you talking about "he." I'm a "she." ;) But, I am glad to see you
agreeing. I thought for a minute there that I must have been confused
if he got it to work with those statements in there.
On 8/26/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> FYI - he's write about the 0 = 0, it's useless
m: Protoculture [mailto:[EMAIL PROTECTED]
Sent: Friday, August 26, 2005 11:39 AM
To: CF-Talk
Subject: Re: SQL Count Question...
thanks RADEMAKERS, that did it... here is the working code..
SELECT COUNT(DISTINCT(opportunity.id))
thanks RADEMAKERS, that did it... here is the working code..
SELECT COUNT(DISTINCT(opportunity.id))
FROMopportunity,
advertiser,
o: CF-Talk
Subject: RE: SQL Count Question...
I don't know about sql server specifically, but you could try syntax
like
select count(distinct(mycolumn)) from mytable
or else you should be able to do
select count(*) from (select distinct(mycolumn) from mytable)
/t
>-Ori
Well, I've never seen that behavior then. But, I also don't understand
what the heck you're trying to do with your query. You realize that
0=0 will always be true, right?
So, when you have a where statement like
0=0 OR (0 LIKE '%' + ',' + CONVERT(VARCHAR,0) + ',' + '%' AND
I don't know about sql server specifically, but you could try syntax
like
select count(distinct(mycolumn)) from mytable
or else you should be able to do
select count(*) from (select distinct(mycolumn) from mytable)
/t
>-Original Message-
>Subject: SQL Count Question...
>
yes I've also tried
'id' AS opportunity.id
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try
I've never seen the 'id' = opportunity.id syntax before. Of course, I
don't use sql server. Do you get the same if you do select
opportunty.id AS id?
On 8/26/05, Protoculture <[EMAIL PROTECTED]> wrote:
> I am trying to get a total count of all our records. The query itself is a
> join ( as you c
ms-sql,
basically want to get the proper number of records.
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with L
type and
version. Also, providing your error message is very helpful.
Dave
-Original Message-
From: Protoculture [mailto:[EMAIL PROTECTED]
Sent: Friday, August 26, 2005 6:47 AM
To: CF-Talk
Subject: SQL Count Question...
I am trying to get a total count of all our records. The query itsel
I am trying to get a total count of all our records. The query itself is a join
( as you can see below )... however when I make a change between the following
I get a different result set... I want to be able to return the correct number
of records but do it the correct way and not return a list
Well, I'll be! It worked. This is the second time that this has
happened to me where the absence of an alias has screwed things up.
Thanks Joe!
George
On 7/8/05, Joe Rinehart <[EMAIL PROTECTED]> wrote:
> Hey George,
>
> AFAIK, derived tables in T-SQL need an alias...try this:
>
> SELECT count(
>>I think it's light years better than the union query
There is still a union, and if the subqueries generate a long list, it
must be pretty unefficient.
--
___
REUSE CODE! Use custom tags;
See http://www.contentbox.com/claude/customtags/tagstore.cfm
(Please
Hey George,
AFAIK, derived tables in T-SQL need an alias...try this:
SELECT count(*)
FROM (
SELECT ObjectID
FROM table1 t1 INNER JOIN table2 t2 ON t1.thisID = t2.thisID
UNION
SELECT ObjectID
FROM table1 t1 INNER JOIN table3 t3 ON t1.thisID = t3.thisID
)
>>I just solved it using an ugly workaround.
Indeed, unless you really need the count to occur in the query, it
should be more efficient to make
two separate queries and just add their record counts in CF.
--
___
REUSE CODE! Use custom tags;
See http://www.c
There you go... it's not ugly - I think it's light years better than the
union query :)
-Original Message-
From: George Abraham [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 07, 2005 4:03 PM
To: CF-Talk
Subject: Re: OT: SQL count of rows from a union query
Ah,
That explains
July 07, 2005 3:59 PM
To: CF-Talk
Subject: Re: SQL count of rows from a union query
Mark,
I am trying to return multiple rows of similarly typed data,
specifically one column named ObjectID. Only, instead of deriving the
number of rows from a getResults.RecordCount, I want to do it in the
SQL que
Ah,
That explains it. I just solved it using an ugly workaround.
SELECT count(*) FROM table1
WHERE ObjectID IN(
SELECT ObjectID
FROM table1 t1 INNER JOIN table2 t2 ON t1.thisID = t2.thisID
UNION
SELECT ObjectID
FROM table1 t1 INNER JOIN table3 t3 ON t1.thisID = t3.th
CSE
> > www.cfwebtools.com
> > www.necfug.com
> > http://mkruger.cfwebtools.com
> >
> >
> >
> >
> > -Original Message-
> > From: George Abraham [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, July 07, 2005 2:22 PM
> > To: CF-Talk
>
> -Original Message-
> From: George Abraham [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 07, 2005 2:22 PM
> To: CF-Talk
> Subject: OT: SQL count of rows from a union query
>
>
> Hi all,
> This one seems to be simple, but it is not working at all
>>
SELECT count(*)
FROM (
SELECT ObjectID
FROM table1 t1 INNER JOIN table2 t2 ON t1.thisID = t2.thisID
UNION
SELECT ObjectID
FROM table1 t1 INNER JOIN table3 t3 ON t1.thisID = t3.thisID
)
AFAIK, one can have a subquery in a WHERE or HAVING clause, but not insid
MCSE
> www.cfwebtools.com
> www.necfug.com
> http://mkruger.cfwebtools.com
>
>
>
>
> -Original Message-
> From: George Abraham [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 07, 2005 2:22 PM
> To: CF-Talk
> Subject: OT: SQL count of rows from a union q
Sent: Thursday, July 07, 2005 2:22 PM
To: CF-Talk
Subject: OT: SQL count of rows from a union query
Hi all,
This one seems to be simple, but it is not working at all. I want to
return a count of the rows from an T-SQL Query that involves a UNION
operation.
SELECT count(*)
FROM (
Hi all,
This one seems to be simple, but it is not working at all. I want to
return a count of the rows from an T-SQL Query that involves a UNION
operation.
SELECT count(*)
FROM (
SELECT ObjectID
FROM table1 t1 INNER JOIN table2 t2 ON t1.thisID = t2.thisID
UNION
SELECT
Charles Heizer wrote:
> No, the database is Oracle.
Well, oid is a system column in PostgreSQL that is often used as
a primary key. Hence my idea you were using PostgreSQL.
Jochem
~|
Find out how CFTicket can increase your comp
No, the database is Oracle. I just tried what you suggested and it works
great!
Thanks!
On 7/5/05 1:09 PM, "Jochem van Dieten" <[EMAIL PROTECTED]> wrote:
> Charles Heizer wrote:
>>
>> SELECT
>> COUNT(a.userid) AS Localusers,
>> (COUNT(a.winadmingroup) = 'YES') AS IsAdmin,
>> a.userid
>>
Charles Heizer wrote:
>
> SELECT
> COUNT(a.userid) AS Localusers,
> (COUNT(a.winadmingroup) = 'YES') AS IsAdmin,
> a.userid
>
> FROM #Session.Schema#.r_localuserlist a, #Session.Schema#.adminsubnet b
>
> WHERE replace(a.userid,'_','~') like replace(b.subnet,'_','~') and
> b.o
Opps,
I did not notice during my copy I did not include the filter. The
adminsubnet table is to filter based on who has rights to a subnet.
- Charles
SELECT
COUNT(a.userid) AS Localusers,
(COUNT(a.winadmingroup) = 'YES') AS IsAdmin,
a.userid
FROM #Session.Schema#.r_localuserlist a, #S
Charles Heizer wrote:
>
> I have a table which contains local user info short name and uid and if it's
> an admin account.
>
> SELECT
> COUNT(a.userid) AS Localusers,
> (COUNT(a.winadmingroup) = 'YES') AS IsAdmin,
> a.userid
> FROM #Session.Schema#.r_localuserlist a, #Session.Schema#.adminsubne
When I do this it it only returns a total of 2 even for the local users.
- charles
On 7/5/05 12:21 PM, "Qasim Rasheed" <[EMAIL PROTECTED]> wrote:
> Have you tried something like this
>
>
> SELECT
> COUNT(a.userid) AS Localusers,
> COUNT(a.winadmingroup) AS IsAdmin,
> a.userid
>
> FROM #Sessi
Have you tried something like this
SELECT
COUNT(a.userid) AS Localusers,
COUNT(a.winadmingroup) AS IsAdmin,
a.userid
FROM #Session.Schema#.r_localuserlist a, #Session.Schema#.adminsubnet b
WHERE b.oid like '#Session.Username#'
and a.winadmingroup = 'YES'
group BY a.userid
On 7/5/05, Charles
Hello,
I'm trying to do a count and I have never needed to really do this before,
so here it goes.
I have a table which contains local user info short name and uid and if it's
an admin account.
SELECT
COUNT(a.userid) AS Localusers,
(COUNT(a.winadmingroup) = 'YES') AS IsAdmin,
a.userid
iting and wanting to change).
Thanks all
Andy
- Original Message -
From: Jeff Langevin <[EMAIL PROTECTED]>
Date: Thu, 16 Sep 2004 16:55:12 -0400
Subject: Re: SQL, count
To: CF-Talk <[EMAIL PROTECTED]>
Ack, id1 and id2 was screwing me up. This works.
SELECT a.id2,
a.name,
(
SELE
Ack, id1 and id2 was screwing me up. This works.
SELECT a.id2,
a.name,
(
SELECT COUNT(b.id1)
FROM tablename b
WHERE a.id2 = b.id1
) as idCount
FROM tablename a
ORDER BY a.id2
On 9/16/2004 4:44 PM, Jeff Langevin wrote:
> Woops... I had a typo I think. The where clause should have been...
>
>
Woops... I had a typo I think. The where clause should have been...
WHERE b.id1 = a.id1
On 9/16/2004 4:40 PM, Jeff Langevin wrote:
> I think this would do it, though I didn't test it.
>
> SELECT a.id2,
> a.name,
> (
> SELECT COUNT(b.id)
> FROM tablename b
> WHERE b.id1 = a.id2
> ) as idCount
I think this would do it, though I didn't test it.
SELECT a.id2,
a.name,
(
SELECT COUNT(b.id)
FROM tablename b
WHERE b.id1 = a.id2
) as idCount
FROM tablename a
ORDER BY a.id2
HTH.
--Jeff
On 9/16/2004 12:47 PM, Andy J wrote:
> I've got a SQL query which for love or money i cant seem to g
>What i basically need to do is get a list of ID2 and a count of the
> people ID1 invited.
Andy,
How about a self join? (The following assumes that the name of your table
is NAMES).
query=qNames
SELECT INVITERS.ID2 AS InviterID, INVITERS.NAME AS InviterName, INVITEES.ID2
AS InviteeID, INVIT
select count(*), id1 from group by id1 ?
-joe
- Original Message -
From: Andy J <[EMAIL PROTECTED]>
Date: Thu, 16 Sep 2004 17:47:15 +0100
Subject: SQL, count
To: CF-Talk <[EMAIL PROTECTED]>
I've got a SQL query which for love or money i cant seem to get my head ar
perhaps this will get you started?
SELECT NAME, id2, COUNT(id1) OVER (PARTITION BY id2)
FROM invite
NAME ID2 COUNT(ID1)OVER(PARTITIONBYID2)
bob 1 2
helen 1 2
Jim 2 1
andy 181 1
On Thu, 16 Sep 2004 17:47:15 +0100, Andy J <[EMAIL PROTECTED]> wrote:
> I've got a SQL query which for love or money i
I've got a SQL query which for love or money i cant seem to get my head around.
The table:
id2 name id1
--
1 andy 181
2 bob 1
3 Jim 2
4 helen 1
To explain what you are looking at a user with the ID 181 invited "And
ssage.
Bob
-Original Message-
From: Julie Clegg [mailto:[EMAIL PROTECTED]]
Sent: May 14, 2001 4:36 PM
To: CF-Talk
Subject: SQL - Count Function Error
Hello,
I am trying to run this MS SQL query and I keep getting the following error:
"The count function requires 1 arguments"...
Count doesn't need the ISNull check. I believe that "Count" will count all
non-null fields so you just need:
count(go.dMonthlyEventDate)
-Original Message-
From: Julie Clegg [mailto:[EMAIL PROTECTED]]
Sent: Monday, May 14, 2001 1:36 PM
To: CF-Talk
Subject: SQL - Count
Hello,
I am trying to run this MS SQL query and I keep getting the following error:
"The count function requires 1 arguments"...
her is my query:
Select cd1.vCodeDecodeDesc as EventTypeDesc,
ISNull(count(go.dMonthlyEventDate, 0)) as EventCount,
IsNull(count(go2.dMonthlyEvent
44 matches
Mail list logo
