Professional Web Applications Themes

Count from a union - MySQL

Given this query: select count(*) from table1 union select count(*) from table2; I get a 2 row answer: +----------+ | count(*) | +----------+ | 3 | | 1 | +----------+ 2 rows in set (0.00 sec) How can I get the sum() of those rows? (i.e. a single answer of 4 in this case)....

  1. #1

    Default Count from a union

    Given this query:

    select count(*) from table1 union select count(*) from table2;

    I get a 2 row answer:

    +----------+
    | count(*) |
    +----------+
    | 3 |
    | 1 |
    +----------+
    2 rows in set (0.00 sec)

    How can I get the sum() of those rows? (i.e. a single answer of 4 in
    this case).
    Derek Fountain Guest

  2. #2

    Default Re: Count from a union

    Derek Fountain wrote:
    > Given this query:
    >
    > select count(*) from table1 union select count(*) from table2;
    >
    > I get a 2 row answer:
    >
    > +----------+
    > | count(*) |
    > +----------+
    > | 3 |
    > | 1 |
    > +----------+
    > 2 rows in set (0.00 sec)
    >
    > How can I get the sum() of those rows? (i.e. a single answer of 4 in
    > this case).
    You need to use a nested query (MySQL > 4.1)

    select sum(c)
    from (
    select count(*) as c from table1
    union
    select count(*) from table2
    ) as dummy;

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: Count from a union

    Giuseppe Maxia wrote:
    > You need to use a nested query (MySQL > 4.1)
    >
    > select sum(c)
    > from (
    > select count(*) as c from table1
    > union
    > select count(*) from table2
    > ) as dummy;

    That worked perfectly, thanks. :o)
    Derek Fountain Guest

Similar Threads

  1. Union Queries In ASP?
    By MDW in forum ASP Database
    Replies: 2
    Last Post: April 25th, 10:23 PM
  2. Two UNION's and an INTERSECT?
    By Anthony Presley in forum Informix
    Replies: 5
    Last Post: November 16th, 04:03 PM
  3. Php - union
    By Ryan A in forum PHP Development
    Replies: 0
    Last Post: August 8th, 01:44 AM
  4. About 'Union'
    By Hawk in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 3rd, 06:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139