I am using below query for fetching result using union of two tables.
I am trying to get total number of records after union of two tables
Need Suggestions.

`

SELECT a.*
FROM svn_headline a where a.newstypeid=2
UNION
SELECT b.*
FROM svn_crimenews b where b.newstypeid=2 order by newsdatetime desc




SELECT count(a.newsid)
FROM svn_headline a where a.newstypeid=2
UNION
SELECT count(b.newsid)
FROM svn_crimenews b where b.newstypeid=2 order by newsdatetime desc

`

Recommended Answers

All 4 Replies

SELECT SUM(num) FROM
(
    SELECT COUNT(*) num FROM svn_headline a where a.newstypeid=2
    UNION
    SELECT COUNT(*) num FROM svn_crimenews b where b.newstypeid=2
)

Getting error

1248 - Every derived table must have its own alias

u can save in a temp table as shown follow

SELECT a.* into Temp_Table
FROM svn_headline a where a.newstypeid=2
UNION
SELECT b.*
FROM svn_crimenews b where b.newstypeid=2 order by newsdatetime desc

and then by count of temp table u get total records

Select count(*) from Temp_Table
SELECT SUM(num) FROM
(
    SELECT COUNT(*) num FROM svn_headline a where a.newstypeid=2
    UNION
    SELECT COUNT(*) num FROM svn_crimenews b where b.newstypeid=2
) Dummy
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.