How to compare two columns in the same query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to compare two columns in the same query?

Author

Topic

fparker
Starting Member

27 Posts

Posted - 2012-01-26 : 10:19:02

I have two tables and I want to count the records by order id and come up with just a list of counts that don't match.

Table Orders
Orderid, Branch, Order
123, 238, 'some data'
123, 238, 'some data'
124, 238, 'some data'

Table Source
Orderid, Branch, Source
123, 238, 'some data'
123, 238, 'some data'
124, 238, 'some data'
124, 238, 'some data'

I am able to write the query that gives me all the results but I just want the differences.

So my result should be

orderid, count of orders, count of source
124, 1, 2

I am getting

orderid, count of orders, count of source
123, 2, 2
124, 1, 2

TIA

--
f

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-26 : 10:33:18

Can you add a where clause to your existing query as


WHERE
   [Count of Orders] <> [Count of Source]

fparker
Starting Member

27 Posts

Posted - 2012-01-26 : 12:18:41

I am having a problem with syntax on that. I am use Sql Server 2000 I beleive.

--
f

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-26 : 12:40:41

The syntax I posted should work on SQL 2000. If you post your query, someone on the forum should be able to suggest what might be wrong.

fparker
Starting Member

27 Posts

Posted - 2012-01-26 : 12:56:18

here you go
This will return all results. I just want the ones that don't match.

declare @branch as varchar(5)
/***************************************/

set @branch = 123
select
orderid
,(select count(*) from order with (nolock) where orderid = x.orderid and branchnum = @branch ) as ordered
,( select count(*) from sourced with (nolock) where lineid in (
select lineid from line where orderid in
(select orderid from order with (nolock) where orderid = x.orderid and branchnum = @branch )) ) as sourced
from order x with (nolock) where branchnum = @branch

group by orderid

results look like this:

orderid ordered sourced
595605 1 1
593517 1 1
591429 1 1
595252 1 1
594208 2 2
596296 1 1
589679 3 3

--
f

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-26 : 13:12:44

You can add the where clause by making your current query into a subquery like this:

declare @branch as varchar(5)
/***************************************/

set @branch = 123 

SELECT * FROM (
select
orderid
,(select count(*) from order with (nolock) where orderid = x.orderid and branchnum = @branch ) as ordered
,( select count(*) from sourced with (nolock) where lineid in (
select lineid from line where orderid in 
(select orderid from order with (nolock) where orderid = x.orderid and branchnum = @branch )) ) as sourced
from order x with (nolock) where branchnum = @branch

group by orderid
) S
WHERE ordered <> sourced

I did not quite understand the subqueries where you are doing the "select lineid" - it was not in the original table structure that you posted. Nonetheless, would something simpler such as the following work for you?

SELECT
	COALESCE(o.OrderId,s.OrderId) AS OrderId,
	o.OrderCount,
	s.SourceCount
FROM
( SELECT OrderId, COUNT(*) AS OrderCount FROM [Orders] GROUP BY OrderId ) o
FULL JOIN
( SELECT OrderId, COUNT(*) AS SourceCount FROM [Source] GROUP BY OrderId) s
	ON s.OrderId = o.OrderId
WHERE o.OrderCount <> s.SourceCount

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources