Help with SQL

• ishaan99

  SSCertifiable

  Points: 6261

  More actions

  June 14, 2007 at 9:30 am

  #117505

  I have a sql from which i need a count of rows for one column which exists in many tables. ex something like

   

  select col1 from tableA union (- has data as AA, BB,CC)

  select col1 from tableB union ( - has data as AA,CC)

  select col1 from tableC  (- has data as DD,EE)

   

  my result should be a total count of 5. . How is it possiblw to get the totalc ount. currently i get the data as AA,BB,CC,DD,EE whereas i am looking for result 5. Any help on this wil lbe greatly appreciated.TIA

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  June 14, 2007 at 10:20 am

  #713007

  You can do it a couple ways

   

  SELECT COUNT(*)

  FROM (SELECT col1 FROM TableA

        union

        SELECT col1 FROM TableB

        union

        SELECT col1 FROM TableC) as DerivedTable

  OR

  SELECT Sum(Number)

  FROM (select count(*) as Number from TableA

        union

        select count(*) from TableB

        union

        select count(*) from TableC) as DerivedTable

• Antares686

  SSC Guru

  Points: 125444

  More actions

  June 14, 2007 at 12:00 pm

  #713029

  I would also suggest UNION ALL instead of UNION as duplicates are deleted from the resultset with just UNION

  Meaning if

  TableA = 100

  TableB = 283

  TableC = 100

  You will get the result 383 not 483 with UNION, UNION ALL will keep all values in the union.