Difference of two count values

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

Difference of two count values

Author

Topic

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2009-04-03 : 11:12:40

Hello,

I have a table which stores the information on several event-types, eg. buys and sells. I would like to count the number of cells which have are a buy-evetn and the number of cells, which are cell events and then print out the differnce of those values.

This is how I count the buys, I don't know how to combine this query with the sell-count (i have no writing properties):

SELECT Count (distinct id) as buys, date
WHERE eventcode ='b'
group by date

Thanks in advance!

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-03 : 11:19:56

[code]SELECT
date,
buys=count( distinct case when eventcode='b' then eventcode end),
sell=count( distinct case when eventcode='s' then eventcode end),
diff=count( distinct case when eventcode='b' then eventcode end)-count( distinct case when eventcode='s' then eventcode end)
FROM
youtable
GROUP BY
date
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 12:42:11

if date field has time part also make sure you strip out time part before grouping

rocco2008
Yak Posting Veteran

63 Posts

Posted - 2009-04-05 : 10:18:44

That doesn't work. It returns a maximum value of 1 for each grouping, looking like this:

1 0 1
1 1 0
1 1 0
1 0 1
1 0 1
1 0 1
1 1 0
1 1 0
1 0 1
1 1 0
1 1 0
1 1 0
1 1 0
1 0 1
1 0 1
1 1 0
1 1 0
1 1 0
1 1 0
1 0 1
1 0 1
1 1 0
1 1 0
1 0 1
1 0 1
1 0 1
1 0 1
1 1 0
1 1 0
1 0 1

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-06 : 10:01:04

You probably have timepart in there too.
Try this,

SELECT 
	DATEADD(DAY, 0, DATEDIFF(DAY, 0, date)) ,
	buys=count( distinct case when eventcode='b' then eventcode end),
	sell=count( distinct case when eventcode='s' then eventcode end),
	diff=count( distinct case when eventcode='b' then eventcode end)-count( distinct case when eventcode='s' then eventcode end)
FROM
	youtable
GROUP BY
	DATEADD(DAY, 0, DATEDIFF(DAY, 0, date))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources