Hi Kay,
Try getting the counts as sub queries, for example
SELECT COUNT(*) AS Artists,
(SELECT COUNT(*) FROM Artworks) AS Artworks,
(SELECT COUNT(*) FROM News) AS News
FROM Artists
Hope this helps a little!
N\
> From: "Kay Smoljak" <[EMAIL PROTECTED]>
> Reply-To: [EMAIL PROTECTED]
> Date: Thu, 9 Nov 2000 09:31:02 +0800
> To: CF-Talk <[EMAIL PROTECTED]>
> Subject: SQL7 count query
>
> I want to list the total number of items in several tables. I was hoping to
> do it in one query, because potentially there could be 10 or more totals and
> running that many queries on one page seems silly. Here's the code I tried:
>
> <cfquery name="count" datasource="#request.dsn#">
> select count(artists.artist_id) as artists,
> count(artworks.artwork_id) as artworks,
> count(news.article_id) as news
> from artists, artworks, news
> </cfquery>
>
> <cfoutput>
> #count.artists# artists listed
> #count.artworks# artworks listed
> #count.news# news articles
> </cfoutput>
>
> But this gives me the total value of all the tables combined for each
> variable. What am I doing wrong?
>
> Thanks,
> K.
> ______________________________________________________
> Kay Smoljak - HTML/ColdFusion Developer - PerthWeb Pty Ltd
> Internet Solutions for your business!
>
> Level 9/105 St George's Tc - Perth - Western Australia
> Ph: (08) 9226 1366 Fax: (08) 9226 1375 Mobile : 0419 949 007
> Visit Perth online! : www.perthweb.com.au
>
> ------------------------------------------------------------------------------
> ------------------
> Archives: http://www.mail-archive.com/[email protected]/
> Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a
> message with 'unsubscribe' in the body to [EMAIL PROTECTED]
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
