Building results from SP - 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

Building results from SP

Author

Topic

leonr
Starting Member

15 Posts

Posted - 2011-11-16 : 07:52:11

Hi Guys, I know i'm new here, but I have tried using google and w3 schools and experimenting to get my required results, but I just can't *quite* get what i'm after.

Here is an example of what I am trying to do, I'm not sure whether to use looping of some kind? Or temporary tables? Or even if there is a way to do this within 1 statement?

A B C
100 1 0
100 0 0
100 0 0
200 1 1
200 1 1

What I am *trying* to do should be simple, but it's really held me up.
Basically I want to return a distinct for col A (so thats easy), but I then also want to return the count of col B and C.

So My results would be..

A B C
100 1 0
200 2 2

I'm struggling to combine count and distinct using SQL.

Any help would be greatly appreciated, i'm used to working with 'normal' code, so i'm in the frame of mind of while loops etc, but it doesn't seem to be best practice with SQL.

Cheers!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-16 : 08:04:40

select A, sum(B), sum(C)
from tbl
group by A

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

leonr
Starting Member

15 Posts

Posted - 2011-11-16 : 08:11:51

Ok cool, that sums that up so to speak! BUT... twisting it slightly, lets say I wanted to return 'yes' if B has a value above 0 ?

Or if I wanted to count how many 5's there were in column B? (I don't want to divide them in the client lol)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-16 : 08:20:13

select A, sum(B), sum(C), case when max(B) > 0 then 'yes' else 'no' end, sum(case when B = 5 then 1 else 0 end)
from tbl
group by A

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

leonr
Starting Member

15 Posts

Posted - 2011-11-16 : 08:25:01

Thankyou very much!! thats great, I need to learn a little more SQL as this is pretty valuable knowledge!! :D

Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources