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 "e;blog"e;::VARCHAR(20) AS tablename,
count(*) FROM blog AS tablecount
UNION
SELECT "e;gallery"e;::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...