Select SUM values - 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

General SQL Server Forums

New to SQL Server Programming

Select SUM values

Author

Topic

mmalaka
Starting Member

33 Posts

Posted - 2013-03-13 : 13:27:41

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-13 : 13:50:47

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.

mmalaka
Starting Member

33 Posts

Posted - 2013-03-14 : 05:20:55

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

mmalaka
Starting Member

33 Posts

Posted - 2013-03-14 : 07:26:08

I fixed this by using Union All instead of Union

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources