General SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesGeneral SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old March 26th, 2005, 07:57 AM
j_freeman j_freeman is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2005
Posts: 11 j_freeman User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 50 m 56 sec
Reputation Power: 0
Question COUNT() from multiple tables?

Is there a way to get the separate number of records of three tables in one query? I was using this one:

Code:
SELECT COUNT(DISTINCT member.id), COUNT(DISTINCT thread.id), COUNT(DISTINCT post.id) FROM member, thread, post;


And it works... except for when the record count of any one of the tables is 0, in which case it will return 0 as the record count for all tables. Using a UNION causes similar problems.

Any ideas?

Reply With Quote
  #2  
Old March 26th, 2005, 09:41 AM
Anibal Anibal is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2005
Posts: 176 Anibal User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 4 h 20 m 48 sec
Reputation Power: 13
Hey j_freeman!
That query returns 0 simply because the FROM t1, t2, t3 makes a combination of all records. Therefore, if a table has no records, the complete combination is null and everything returns 0.

Is there a relationship between the tables or are they simply three tables? (I ask about FKs and that).

There are many ways to aproach this (if the tables are not related):

SELECT
(SELECT COUNT(DISTINCT id) FROM member) AS members,
(SELECT COUNT(DISTINCT id) FROM thread) AS threads,
(SELECT COUNT(DISTINCT id) FROM post) AS posts

another way is to write down a Stored Procedure and asign every count to a variable. Then, return:

SELECT @count1, @count2, @count3

If the tables are related a succesive call to a left join would do the trick!

Good Luck!

ANibal.

Reply With Quote
  #3  
Old May 3rd, 2005, 12:00 AM
j_freeman j_freeman is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2005
Posts: 11 j_freeman User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 50 m 56 sec
Reputation Power: 0
Quote:
Originally Posted by Anibal
Hey j_freeman!
That query returns 0 simply because the FROM t1, t2, t3 makes a combination of all records. Therefore, if a table has no records, the complete combination is null and everything returns 0.

Is there a relationship between the tables or are they simply three tables? (I ask about FKs and that).

There are many ways to aproach this (if the tables are not related):

SELECT
(SELECT COUNT(DISTINCT id) FROM member) AS members,
(SELECT COUNT(DISTINCT id) FROM thread) AS threads,
(SELECT COUNT(DISTINCT id) FROM post) AS posts

another way is to write down a Stored Procedure and asign every count to a variable. Then, return:

SELECT @count1, @count2, @count3

If the tables are related a succesive call to a left join would do the trick!

Good Luck!

ANibal.


Hello Anibal.

Yes the tables are indeed related. Here's the relevant structure of the three tables:

MEMBER
id - int(10)

THREAD
id - int(10)
author - int(10) <-- This corresponds to the ID field of the MEMBER table.

POST
id - int(10)
parent - int(10) <-- This corresponds to the ID field of the THREAD table.
author - int(10) <-- This corresponds to the ID field of the MEMBER table.

I'm now using this query:
Code:
SELECT COUNT(DISTINCT member.id), COUNT(DISTINCT thread.id), COUNT(DISTINCT post.id) FROM member LEFT JOIN thread ON (member.id=thread.author) LEFT JOIN post ON (member.id=post.author)


And I think it works for what I need.

Reply With Quote
  #4  
Old July 10th, 2010, 07:48 PM
mannuthareja mannuthareja is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2010
Posts: 2 mannuthareja User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 10 m 52 sec
Reputation Power: 0
Hi,

I am using similar query....
SELECT
(SELECT COUNT(DISTINCT id) FROM member) AS members,
(SELECT COUNT(DISTINCT id) FROM thread) AS threads,
(SELECT COUNT(DISTINCT id) FROM post) AS posts

However, this returns an error --> Reserved error (-3025); there is no error message.

Please can you advise why it is not working (on Ms-Access 2003)

Regards,
Mannu


Quote:
Originally Posted by Anibal
Hey j_freeman!
That query returns 0 simply because the FROM t1, t2, t3 makes a combination of all records. Therefore, if a table has no records, the complete combination is null and everything returns 0.

Is there a relationship between the tables or are they simply three tables? (I ask about FKs and that).

There are many ways to aproach this (if the tables are not related):

SELECT
(SELECT COUNT(DISTINCT id) FROM member) AS members,
(SELECT COUNT(DISTINCT id) FROM thread) AS threads,
(SELECT COUNT(DISTINCT id) FROM post) AS posts

another way is to write down a Stored Procedure and asign every count to a variable. Then, return:

SELECT @count1, @count2, @count3

If the tables are related a succesive call to a left join would do the trick!

Good Luck!

ANibal.

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesGeneral SQL Development > COUNT() from multiple tables?


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap