SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select SUM values		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mmalaka
Starting Member

Ireland
33 Posts
Posted - 03/13/2013 :  13:27:41  Show Profile  Visit mmalaka's Homepage  Reply with Quote
I have 2 identical tables in 2 databases. I want to get the count of each values in both databases, so I am doing this:


select distinct(FIELD1) FieldValue, sum(counter) ValueCount  from (  
select distinct(FIELD1) COLLATE Latin1_General_CS_AS FIELD1, count(FIELD1) counter from TABLE1 group by FIELD1 
union  
select distinct(FIELD1) COLLATE Latin1_General_CS_AS FIELD1, count(FIELD1) counter from DB2.dbo.TABLE1 group by FIELD1
) t  group by t.FIELD1


but it is not working as In both tables I have a value 1000 so I am expecting to get:

FieldValue ValueCount
1000 2

But instead I am getting:
FieldValue ValueCount
1000 1

Any advice please

Lamprey
Flowing Fount of Yak Knowledge

3831 Posts
Posted - 03/13/2013 :  13:50:47  Show Profile  Reply with Quote
Maybe try removing the DISTINT:
select 
	FIELD1 AS FieldValue
	,sum(counter) ValueCount  
from 
	(  
		select FIELD1 COLLATE Latin1_General_CS_AS , count(FIELD1) counter from TABLE1 group by FIELD1 
		union  
		select FIELD1 COLLATE Latin1_General_CS_AS , count(FIELD1) counter from DB2.dbo.TABLE1 group by FIELD1
	) AS t  
group by 
	t.FIELD1
Otherwise please post sample data.
Go to Top of Page

mmalaka
Starting Member

Ireland
33 Posts
Posted - 03/14/2013 :  05:20:55  Show Profile  Visit mmalaka's Homepage  Reply with Quote
I tried removing the distinct but it did not work...

The Field1 is of type varchar(50) in both databases and it include the following values in both databases
100001
100002
100003
100004
100005

in the first database we have
100007
100008

In the 2nd database we have
100009
100010

So I am looking for the following results

FieldValue FieldCount

100001 2
100002 2
100003 2
100004 2
100005 2
100007 1
100008 1
100009 1
100010 1


Go to Top of Page

mmalaka
Starting Member

Ireland
33 Posts
Posted - 03/14/2013 :  07:26:08  Show Profile  Visit mmalaka's Homepage  Reply with Quote
I fixed this by using Union All instead of Union
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000