Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: UNION ALL Column Heading Names

    These three queries run fine except for the column headings.

    NewListings
    1474
    795
    821

    The 795 should show NewPendings
    The 821 should show ClosedSales

    What am I missing here?

    SELECT DISTINCTROW Sum(qryDesertSTATS_LED.CountOfCity1) AS [New Listings]
    FROM qryDesertSTATS_LED

    UNION ALL

    SELECT DISTINCTROW Sum([qryDesertSTATS_PEND].[CountOfCity1]) AS [New Pendings]
    FROM qryDesertSTATS_PEND;

    UNION ALL

    SELECT DISTINCTROW Sum([qryDesertSTATS_CLOSD].[CountOfCity1]) AS [New Closed]
    FROM qryDesertSTATS_CLOSD;


    Thanks Rick

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Rick Schreiber View Post
    What am I missing here?
    in a UNION query, the result set is actually a table, and this table's column names are taken from the first SELECT in the UNION

    so you could add a literal to each SELECT, e.g.
    Code:
    SELECT SUM(CountOfCity1)  AS total
         , 'New Listings'     AS totaltype
    FROM qryDesertSTATS_LED
    UNION ALL
    SELECT SUM(CountOfCity1)
         , 'New Pendings'
    FROM qryDesertSTATS_PEND;
    UNION ALL
    SELECT SUM(CountOfCity1)
         , 'New Closed'
    FROM qryDesertSTATS_CLOSD;
    note you did not need DISTINCTROW because each of your SELECTs is producing only one row (the one with the sum on it) so of course it's gonna be distinct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Smile Union all

    Darn . . . I hate when I'm stupid!

    It's very much like the SORT I asked about a few weeks ago.

    Thanks Rudy . . .

Posting Permissions

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