COUNT question - 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

COUNT question

Author

Topic

AF279
Starting Member

2 Posts

Posted - 2008-02-26 : 08:50:37

Hi,

I'm new to the forum and found some great information on here. I used SQL at University some years ago but can't remember much. My problem is the following:

I want to do a COUNT for TWO columns.

I then want to see if the first column is great than the second column for a particular value.

I then need to do the exact same clause for the other column where that value is also present.

So I need to find out where X>Y for BOTH columns. Currently I can only do an individual one and I was wondering if anyone else knew how I could maybe combine both COUNTs?

Many thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 08:54:52

Something like

select
sum(case when X>Y then 1 else 0 end) as [X>Y],
sum(case when Y>X then 1 else 0 end) as [Y>X]
from your_table

or post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

AF279
Starting Member

2 Posts

Posted - 2008-02-26 : 09:11:50

Thanks I will try that but how do I add the two of them together? Sorry if that sounds like such a basic question but I just want to make sure.

I tried this but it didn't work:

SELECT SUM(case when a.home_goals>a.away_goals then 1 else 0 end) as [X>Y],
SUM(case when a.away_goals>a.home_goals then 1 else 0 end) as [X>Y]
FROM sunday_league_results

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-02-26 : 17:59:47

change the comma to a + ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-26 : 18:02:24

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-26 : 19:10:06