Count and UNION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count and UNION

Author

Topic

tomislavg
Yak Posting Veteran

51 Posts

Posted - 2011-01-25 : 03:07:30

Dear All, I have two tables.

TBL1 TBL2
col1 2col1
col2 2col2
col3 2col3

They do not have any relationships to each other except the data in
col2 is the same type as the 2col2. I want to do count of both fields. It is nvarchar. I am not sure how to do it to get results from both tables together.

For one table it is not a problem:

select col2, count(col2) from TBL1
group by col2

Any idea for two tables?

Thanks a lot,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-25 : 03:09:32

SELECT COUNT(*)
FROM (SELECT Col2 FROM TBL1 UNION ALL SELECT Col2 FROM TBL2) AS d

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-25 : 03:10:39

SELECT SUM(i) FROM (SELECT COUNT(Col2) FROM TBL1 UNION ALL SELECT COUNT(Col2) FROM TBL2) AS d(i)

N 56°04'39.26"
E 12°55'05.63"

tomislavg
Yak Posting Veteran

51 Posts

Posted - 2011-01-25 : 03:17:50

Can I do also....

SELECT col2, COUNT(*)
FROM (SELECT Col2 FROM TBL1 UNION ALL SELECT Col2 FROM TBL2) AS d

Thank you Pezo but I need something like:

SELECT Int_Section, COUNT(Int_Section)
FROM (SELECT Int_Section FROM Int_Staff UNION ALL SELECT Nat_Section FROM Nat_Staff)

tomislavg
Yak Posting Veteran

51 Posts

Posted - 2011-01-25 : 04:03:45

quote:
Originally posted by tomislavg

Can I do also....

SELECT col2, COUNT(*)
FROM (SELECT Col2 FROM TBL1 UNION ALL SELECT Col2 FROM TBL2) AS d

Thank you Peso but I need something like:

SELECT Int_Section, COUNT(Int_Section)
FROM (SELECT Int_Section FROM Int_Staff UNION ALL SELECT Nat_Section FROM Nat_Staff)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-25 : 04:11:07

SELECT Int_Section, COUNT(*)
FROM (SELECT Int_Section FROM Int_Staff UNION ALL SELECT Nat_Section FROM Nat_Staff)
GROUP BY Int_Section

N 56°04'39.26"
E 12°55'05.63"

tomislavg
Yak Posting Veteran

51 Posts

Posted - 2011-01-25 : 04:53:19

Thank you Peso but I get
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'GROUP'.

quote:
Originally posted by Peso

SELECT Int_Section, COUNT(*)
FROM (SELECT Int_Section FROM Int_Staff UNION ALL SELECT Nat_Section FROM Nat_Staff)
GROUP BY Int_Section

N 56°04'39.26"
E 12°55'05.63"

tomislavg
Yak Posting Veteran

51 Posts

Posted - 2011-01-25 : 05:08:33

I got it:

SELECT Int_Section, COUNT(Int_Section) as Total
FROM (SELECT Int_Section FROM Int_Staff UNION ALL SELECT Nat_Section FROM Nat_Staff)as d
GROUP BY Int_Section
ORDER BY Total desc

quote:
Originally posted by tomislavg

Thank you Peso but I get
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'GROUP'.

quote:
Originally posted by Peso

SELECT Int_Section, COUNT(*)
FROM (SELECT Int_Section FROM Int_Staff UNION ALL SELECT Nat_Section FROM Nat_Staff)
GROUP BY Int_Section

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources