How to get the total rows on a database with one query

Question

I have a database with two tables, I want to get the total of rows in those tables with a single query. So far T tried:

SELECT (count(bill.*) + count(items.*)) as TTL FROM bill, items // Failed 
SELECT count(*) as TTL FROM bill, items // wrong total
SELECT (count(bill.ID_B) + count(items.ID_I)) as TTL FROM bill, items // wrong total
SELECT count(bill.ID_B + items.ID_I) as TTL FROM bill, items // return the biggest total

http://stackoverflow.com/questions/606234/select-count-from-multiple-tables — Tim Schmelter, Sep 28 '12 at 23:30

score 3 · Accepted Answer · answered Sep 28 '12 at 23:29

3

Use two sub-queries:

select (select count(1) from bill) + (select count(1) from items);

answered Sep 28 '12 at 23:29

user229044

232,980
40
330
338

@JDwyer Would you explain Why? – SIFE Sep 29 '12 at 00:55
Now-a-days the databases probably make these operate the same. The conventional wisdom was that selecting a constant a.k.a 1 or 'a' is faster than selecting values from the table. See: http://stackoverflow.com/questions/1221559/count-vs-count1 – JDwyer Sep 29 '12 at 01:08

How to get the total rows on a database with one query

1 Answers1