Count or Sum?
-
This is most likely a simple question, but I'm new to the SQL and after searching several books for the answer, thought I'd try the message board.
I have two tables that I'm pulling Last name, First name and Employee ID from. I want to count the total of all records within both tables of active employees.
Below is the code I have:
SELECT COUNT(*) AS NUM
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT COUNT(*) AS NUM
FROM table2
WHERE EmpTermDate IS NULL
I get two counts in the result set and would like to get a total. Any help is appreciated.
-
create table #temp1(num int)-- cna uses a derived table too
insert into #temp1
SELECT COUNT(*) AS NUM
FROM table1
WHERE EmpTermDate IS NULL
UNION all
SELECT COUNT(*) AS NUM
FROM table2
WHERE EmpTermDate IS NULL
select sum(num) from #temp1
-
OR:
SELECT SUM(NUM) AS Total
FROM(
SELECT COUNT(*) AS NUM
FROM table1
WHERE EmpTermDate IS NULL
UNION all
SELECT COUNT(*) AS NUM
FROM table2
WHERE EmpTermDate IS NULL )
OR
SELECT (SELECT COUNT(*) AS NUM
FROM table1
WHERE EmpTermDate IS NULL )
+
(
SELECT COUNT(*) AS NUM
FROM table2
WHERE EmpTermDate IS NULL ) AS Total
HTH
* Noel -
I'd with with Noeld's solutions as they don't use a #temp table which would really not be a best pratice in this case.
-
I'd union the two datasets BEFORE counting ...
SELECT COUNT(*) AS NUM
FROM
(
SELECT EmployeeID
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT EmployeeID
FROM table2
WHERE EmpTermDate IS NULL
) AS UT
Julian Kuiters
juliankuiters.id.au -
That would depend on what it is to be counted..
The poster asked to count 'records'... (though it's not entirely clear what the actual intention is
)UNION before count will count distinct empid's.
UNION ALL before count will count records. (assuming 'records' in this case is synonymous with 'rows')
/Kenneth
-
Thanks for everyone's feedback!!
I used Julian's suggestion because I was trying to only count distinct records. It is true, I did not include this in my original post, because my first task was to try and at least just get a count in a single result. Thanks, again for everyone's feedback. The combination of thoughts and ideas, helped give me a solution while also learning SQL.
Here was the code I'm using:
SELECT COUNT(*) AS NUM
FROM
(
SELECT EmpID
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT EmpNo
FROM table2
)
AS UT
My last question is... What is UT? Union Total? It checks out with the Syntax checker with Enterprise Manager, but the help system does not mention the UT syntax?
-
"UT" probably stood for "Union Table" in the mind of the author. However, here it is just a table alias so it makes not different.
The SQL:
SELECT EmpID
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT EmpNo
FROM table2
is a derived table or in-line view where you are essentially building a table on the fly. This is why you were able to "SELECT * FROM" it. Part of SQL's syntax requires that you name this table. You could have named it whatever you want.
-
UT is the alias of the derived table. You can alias tables just like you alias columns.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply