Adding a total amount from 3 tables

Question

Post reply

Adding a total amount from 3 tables

SQLbeginner

SSC Eights!

Points: 918
More actions
January 24, 2008 at 9:23 am

#186055

Hello,
I am trying to retrieve the total amount of people listed in 3 tables. The tables are Undergraduates, Graduates and Faculty. The one common column in each table is "loginid".
I have tried this script to retrieve the total:
"" Select count(distinct u.loginid) as Total
from undergradstudents u
UNION
Select count (distinct f.loginid) as Total
From Faculty f
UNION
Select count (distinct g.loginid) as Total
From Gradstudents g ""
However, instead of a result with one row, I get a result with 3 rows, i.e. the totals for each table.
My question is, what can i add/change to the script to get the final amount?
Any help would be great, thanks.
Vinni

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

rchagoor Grasshopper Points: 17 More actions · Answer 1

Try the below code.

Select SUM(Total)

From

(Select count(distinct u.loginid) as Total

from undergradstudents u

UNION ALL

Select count (distinct f.loginid) as Total

From Faculty f

UNION ALL

Select count (distinct g.loginid) as Total

From Gradstudents g) Tot

SQLbeginner SSC Eights! Points: 918 More actions · Answer 2

Thank you rchagoor!

Don't know why I didn't think of that.

Regards,

Vinni

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 3

Actually I wouldn't use UNION ALL unless you know you aren't going to get duplicates (or you want to count duplicates)

Also It would probably be easier to do the following

SELECT COUNT(1) AS Total

FROM (

SELECT LoginId FROM UndergradStudents

UNION

SELECT LoginId FROM Faculty

UNION

SELECT LoginId FROM Gradstudents

) x

The UNION will take care of your DISTINCT for you, and there is no need to do count(loginid) because you are essentially pulling the number of rows anyway. When you can use it COUNT(1) is fractionally faster than COUNT(*) or anything else for that matter. Wiser heads than mine might know why 🙂

And no offence but this looks like homework .. I'll give you credit for having tried yourself though. Assuming it is homework and you are new to SQL, you may want to watch your formatting. Its something I struggle with myself (and so do alot of other coders I know) but it makes your code more readable and professors like it :). Not to mention the poor schlub who has to read your code 5 years after you've left your job and its stopped working.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]