UNION ALL Count Query

[k09]

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.

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)

[swampBoogie]

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

[k09]

thanks!

What does the dt at the end mean?

[r937]

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

[k09]

thanks!