Multiple select statements in Single query

Question

I am generating a report in php (mysql),

ex:

`select count(id) as tot_user from user_table
 select count(id) as tot_cat from cat_table
 select count(id) as tot_course from course_table`

Like this I have 12 tables.

Can i make it in single query. If i did? Process gets slow?

For MyISAM tables there is even a far better way, see my answer, which is faster. — Pentium10, Feb 13 '10 at 09:52

score 297 · Accepted Answer · edited Jul 06 '20 at 13:51

297

SELECT  (
    SELECT COUNT(*)
    FROM   user_table
) AS tot_user,
(
    SELECT COUNT(*)
    FROM   cat_table
) AS tot_cat,
(
    SELECT COUNT(*)
    FROM   course_table
) AS tot_course

edited Jul 06 '20 at 13:51

DanB

2,022
1
12
24

answered Nov 21 '09 at 11:46

sathish

6,705
3
30
35

For MyISAM tables there is even a far better way, see my answer. – Pentium10 Feb 13 '10 at 09:45
5

"Operand should contain 1 column(s)" - only if your merged tables differ in columns count. They should match. 1 column per table in this example. – Zon Apr 22 '16 at 10:54
7

this only works if you're returning a single output from each sub-query – Prachi Jan 23 '19 at 21:44
@Prachi what is the solution for returning multiple columns? – Aslam H Jan 04 '21 at 02:05

score 26 · Answer 2 · answered Feb 13 '10 at 09:37

26

If you use MyISAM tables, the fastest way is querying directly the stats:

select table_name, table_rows 
     from information_schema.tables 
where 
     table_schema='databasename' and 
     table_name in ('user_table','cat_table','course_table')

If you have InnoDB you have to query with count() as the reported value in information_schema.tables is wrong.

answered Feb 13 '10 at 09:37

Pentium10

204,586
122
423
502

2

In case you are wondering, see also [these answers about the difference between MyISAM and InnoDB](https://stackoverflow.com/questions/20148/myisam-versus-innodb). – Paul Rougieux Aug 17 '17 at 10:51

score 20 · Answer 3 · edited Jan 01 '12 at 22:35

You can certainly us the a Select Agregation statement as Postulated by Ben James, However This will result in a view with as many columns as you have tables. An alternate method may be as follows:

SELECT COUNT(user_table.id) AS TableCount,'user_table' AS TableSource FROM user_table
UNION SELECT COUNT(cat_table.id) AS TableCount,'cat_table' AS TableSource FROM cat_table
UNION SELECT COUNT(course_table.id) AS TableCount, 'course_table' AS TableSource From course_table;

The Nice thing about an approch like this is that you can explicitly write the Union statements and generate a view or create a temp table to hold values that are added consecutively from a Proc cals using variables in place of your table names. I tend to go more with the latter, but it really depends on personal preference and application. If you are sure the tables will never change, you want the data in a single row format, and you will not be adding tables. stick with Ben James' solution. Otherwise I'd advise flexibility, you can always hack a cross tab struc.

score 11 · Answer 4 · edited Aug 02 '12 at 14:01

11

select RTRIM(A.FIELD) from SCHEMA.TABLE A where RTRIM(A.FIELD) =  ('10544175A') 
 UNION  
select RTRIM(A.FIELD) from SCHEMA.TABLE A where RTRIM(A.FIELD) = ('10328189B') 
 UNION  
select RTRIM(A.FIELD) from SCHEMA.TABLE A where RTRIM(A.FIELD) = ('103498732H')

edited Aug 02 '12 at 14:01

sloth

99,095
21
171
219

answered Aug 19 '11 at 21:10

net.tunneler

159
1
2

12

What question is answered by this? – Oliv Oct 17 '14 at 06:40
1

This is a combination of Miguel Castaneda's UNION() solution and Pentium10's INFORMATION_SCHEMA solution. Please cite answers you use. – HoldOffHunger Aug 23 '18 at 20:54

score 6 · Answer 5 · answered Jun 18 '19 at 12:41

SELECT t1.credit, 
       t2.debit 
FROM   (SELECT Sum(c.total_amount) AS credit 
        FROM   credit c 
        WHERE  c.status = "a") AS t1, 
       (SELECT Sum(d.total_amount) AS debit 
        FROM   debit d 
        WHERE  d.status = "a") AS t2

score 2 · Answer 6 · answered Oct 05 '18 at 13:53

I know this is an old stack but i will post this Multi-SQL select case

    SELECT bp.bizid, bp.usrid, bp.website, 
ROUND((SELECT SUM(rating) FROM ratings WHERE bizid=bp.bizid)/(SELECT COUNT(*) FROM ratings WHERE bizid=bp.bizid), 1) AS 'ratings', 
(SELECT COUNT(*) FROM bzreviews WHERE bizid=bp.bizid) AS 'ttlreviews', 
bp.phoneno, als.bizname, 
(SELECT COUNT(*) FROM endorsment WHERE bizid=bp.bizid) AS 'endorses'
, als.imgname, bp.`location`, bp.`ownership`, 
(SELECT COUNT(*) FROM follows WHERE bizid=bp.bizid) AS 'followers', 
bp.categories, bp.openhours, bp.bizdecri FROM bizprofile AS bp 
INNER JOIN alluser AS als ON bp.usrid=als.userid 
WHERE als.usertype='Business'

Multiple select statements in Single query

6 Answers6

Linked

Related