CODEWITHSUNDEEP
sqldatabasepostgresqlselect
bharatbang
bharatbang

Reputation: 43

SQL Union displaying wrong result

SELECT COUNT(id)
FROM table1
UNION
SELECT COUNT(id)
FROM table2
UNION
SELECT COUNT(id)
FROM table3

Result is 

247811

58599

76

But actually

table1 has 247811 rows

table2 has 76 rows

table3 has 58599 rows

Upvotes: 0

Views: 151

Answers (2)

Vecchiasignora
Vecchiasignora

Reputation: 1315

if your problem depends on order by please order by your select after union, if you have a problem with count?(in your example there is a different count 79 and 76) it depends on that you use count(id) it is not same is count(*), count(id) ignores every null in Id column, count(*) it is count of your table rows

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

The union operator makes no gaurantees about the order. If you want to order the results in a particular way, you'd have to do so explicitly, with an order by clause. Note also that union removes duplicates, so you'd better use union all. E.g.:

SELECT   cnt
FROM     (SELECT 't1', COUNT(id) FROM table1
          UNION ALL
          SELECT 't2', COUNT(id) FROM table2
          UNION ALL
          SELECT 't3', COUNT(id) FROM table3) t
ORDER BY 1 ASC

Upvotes: 2

Related Questions