SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2006
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    UNION ALL Count Query

    Hi,

    How I want to get the total count from the below queries and add them together.
    How do I do this?

    The first part of the UNION cluase returns 4
    the next part returns 1
    But I want to only return 5.

    Is this possible?

    Thanks.

    Code:
    SELECT DISTINCT COUNT(p.Portfolio) AS FundTotal
    FROM         Staff AS s INNER JOIN
                          Portfolio AS p ON p.PortfolioID = s.FundID
    WHERE     (s.Name = @Name)
    
    UNION ALL
    
    SELECT DISTINCT COUNT(FundID) AS FundTotal
    FROM         Access AS a
    WHERE     (Name = @Name) AND (SelDate = @Date)

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select sum(fundtotal) as s
      from (
    SELECT COUNT(p.Portfolio) AS FundTotal
    FROM   Staff AS s INNER JOIN
           Portfolio AS p ON p.PortfolioID = s.FundID
    WHERE  s.Name = @Name
    UNION ALL
    SELECT COUNT(FundID) 
    FROM   Access AS a
    WHERE  Name = @Name
      AND  SelDate = @Date) dt

  3. #3
    SitePoint Evangelist
    Join Date
    Oct 2006
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks!

    What does the dt at the end mean?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by k09 View Post
    What does the dt at the end mean?
    what it is, is a table alias

    what it means, is "derived table"

    you can assign any alias name you want here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Oct 2006
    Posts
    408
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks!


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
  •