Show count of multiple tables in one query

Question

I have a question, I want to get counts of different tables in one query to show on a page...

This the query i wrote, But it's not providing correct result..

SELECT COUNT(U.uID) AS total, COUNT(P.cID) AS Catmame FROM `dev_web_user` AS U, `dev_web_categories` AS P

Please note all count is independent, now join etc...

Please help me on this...

Thanks & Best Regards,

Gerardo Charles Rojas Vega · Answer 1 · 2014-12-10T14:47:28.870

0

So try, this:

SELECT 
  (SELECT COUNT(U.uID) FROM `dev_web_user` AS U) as total,
  (SELECT COUNT(P.cID) FROM `dev_web_categories` AS P) as Catmame;

edited Dec 10 '14 at 14:47

answered Dec 10 '14 at 14:41

1

reading from the returned array must be reversed (at least on Oracle) – Halayem Anis Dec 10 '14 at 14:42
but i need all the counts as separate column a signal column.. is it possible? – DeDevelopers Dec 10 '14 at 14:43
I've edit my answer and by checking.. this maybe duplicate: http://stackoverflow.com/questions/3761240/count-from-multiple-tables-in-mysql – Gerardo Charles Rojas Vega Dec 10 '14 at 14:49
SELECT without FROM ?? which database accepts this query ? – Halayem Anis Dec 10 '14 at 14:49
@HalayemAnis As [SELECT Syntax](http://dev.mysql.com/doc/refman/5.0/en/select.html) shows FROM clause is optional – Gerardo Charles Rojas Vega Dec 10 '14 at 15:01

Raging Bull · Accepted Answer · 2014-12-10T14:57:38.997

0

You can do this by joining them as two different temporary tables.

Try this way:

SELECT  * FROM
(SELECT COUNT(*) AS total FROM `dev_web_user`) T1 JOIN
(SELECT COUNT(*) AS Catmame FROM `dev_web_categories`) T2 ON 1=1

Sample Output:

TOTAL   CATMAME
4       8

See working demo in SQL Fiddle.

edited Dec 10 '14 at 14:57

answered Dec 10 '14 at 14:52

Raging Bull

2 Answers2