Derived query from a Union

[lmasi02]

Hi All,

How can i write a query to count the number of records from a derived table. the derived table is a union of two table.

select msisdn from enrollOffer union DISTINCT select msisdn from enrollHistory

[guido2004]

SELECT COUNT(*)
FROM 
  (SELECT
     msisdn 
   FROM enrollOffer 
   UNION 
   SELECT 
     msisdn 
   FROM enrollHistory
  )

If you want to count msisdn that are present in both tables as well, use UNION ALL.

[lmasi02]

SELECT COUNT(*)
FROM 
  (SELECT
     msisdn 
   FROM enrollOffer 
   UNION 
   SELECT 
     msisdn 
   FROM enrollHistory
  )

If you want to count msisdn that are present in both tables as well, use UNION ALL.

I tried this and am getting the error

ERROR 1248 (42000): Every derived table must have its own alias

[guido2004]

SELECT COUNT(*)
FROM 
  (SELECT
     msisdn 
   FROM enrollOffer 
   UNION 
   SELECT 
     msisdn 
   FROM enrollHistory
  ) AS a

Ah yes, forgot the alias

[lmasi02]

I have tried to put the alian as

SELECT COUNT(cout) FROM (SELECT msisdn as cout FROM enrollOffer UNION SELECT msisdn as cout FROM enrollHistory );

but it is all failing. same error message

[r937]

have a look at exactly where guido put the alias -- compare post #2 and post #4