Page 1 of 1

Joining count()'s

Posted: Thu Jul 28, 2005 12:11 pm
by josh
Sorry if this has been gone over before, if so could you post a link I couldn't find anything on the search feature

What I want to do is select the count(*) of multiple tables I can't seem to get it quite right.

Code: Select all

SELECT count( * )
FROM `blog`
JOIN count( * )
FROM `gallery`
LIMIT 0 , 30
Something like that, any links to articles that have explained this? Should I be using JOIN?

How would I do more then 2 tables?

Thanks

Posted: Thu Jul 28, 2005 12:33 pm
by nielsene
Cna you show an example output row that you want? I'm not sure I understand the question.

Posted: Thu Jul 28, 2005 12:37 pm
by nielsene
Perhaps

Code: Select all

SELECT &quote;blog&quote;::VARCHAR(20) AS tablename,
       count(*) FROM blog AS tablecount
UNION
SELECT &quote;gallery&quote;::VARCHAR(2) AS tablename,
       count(*) FROM gallery AS tablecount;
but that looks ugly to me. But is it what your looking for?

Are you using MySQL? If yes, do you know if it implements the INFORMATION_SCHEMA? You might be able to query that. I'll try to draft up a mock query from PostGreSQL where I have more experience....

Posted: Thu Jul 28, 2005 12:40 pm
by josh
I have links in my admin panel for displaying items that need approval, may it be in a pre-mod'd forum, my image gallery.. etc...

Instead of doing a seperate query for each table:

Code: Select all

$blogs=mysql_query("SELECT count(*) FROM `blogs`");
$users=mysql_query("SELECT count(*) FROM `users`");
$gallery=mysql_query("SELECT count(*) FROM `gallery` WHERE `approved` = 0 ");
$gallery_total=mysql_query("SELECT count(*) FROM `gallery`");
I was hoping to nail it all in one shot, select the total # of rows from each table.

Posted: Thu Jul 28, 2005 12:46 pm
by nielsene

Code: Select all

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES
   WHERE table_name IN('blogs','users','gallery');
Doesn't help with the 'WHERE approved=0' one though....

Posted: Thu Jul 28, 2005 12:49 pm
by josh
nielsene wrote:

Code: Select all

SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES
   WHERE table_name IN('blogs','users','gallery');
Doesn't help with the 'WHERE approved=0' one though....
2 queries is still better then 4!! Thanks for the help.

Posted: Thu Jul 28, 2005 1:03 pm
by nielsene
One other option, probably not right in this case, but something to keep in mind:

Code: Select all

CREATE TABLE pending_actions (
  tablename TEXT NOT NULL,
  table_key TEXT NOT NULL,
  pending_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (tablename, table_key)
);
-- some stored procedures to insert/delete rows into this table
-- on other table inserts/updates/deletes
SELECT tablename, count(table_key) FROM pending_actions GROUP BY tablename;
Its not too clean, and typing issues are a bear. (I routinely use "natural" primary keys so my keys can be strings, or ints, or whatever was appropriate for that table. PostGreSQL does a good job casting as needed, so sticking all the keys into a TEXT column isn't too bad, but its nothing I would do too often.) But it would be one way to help consolidate.


Or create a view using the select/union method I showed before with as many unions as needed. That one could have the extra conditions on the where as needed.... Then just do a select * from the view...