How to create loop without loop [sql] ? - 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 create loop without loop [sql] ?

Author

Topic

deathrone
Starting Member

15 Posts

Posted - 2012-12-21 : 04:24:02

i've got f.x. sth like this:

First column: Morderer_id.
Second column: shoots_No
Third columnt: victim_id

01 1 A
01 2 A
01 3 A
01 kill A
01 1 B
01 2 B
01 1 C
01 kill C
02 1 D
02 2 D
02 3 D
02 4 D
02 kill D
02 1 E
02 1 F
02 1 G
02 2 G

AND I would like to count how much avg( shoots ) the morderer needs to kill victim. For example to kill victim A morderer 01 needed 4 shoots to kill C only 2.
Im tryin to make this with lag statement but Im afraid iits not enoygh. Please Help good people!

trololoolo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 04:26:10

whats the primary key or unique valued column of table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 04:30:22

[code]
SELECT Morderer_Id,SUM(ShootCnt)*1.0/COUNT(*) AS AvgShoots
FROM
(
SELECT Morderer_id,victim_id,SUM(CASE WHEN shoot_no <> 'Kill' THEN 1 ELSE 0 END) AS ShootCnt
FROM table
GROUP BY Morderer_id,victim_id
HAVING SUM(CASE WHEN shoot_no = 'Kill' THEN 1 ELSE 0 END) >0
)t
GROUP BY Morderee_Id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-21 : 05:26:13

Hi visakh,
>>to kill victim A morderer 01 needed 4 shoots to kill C only 2.
Your inner query returning noOfShoots as 3 only for morderer 01(for A victim) ANd 1 for C

I mean,
You missed that last attempt count there...
So it should be
SUM(CASE WHEN shoot_no <> 'Kill' THEN 1 ELSE 0 END)+1 AS ShootCnt

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 05:29:28

quote:
Originally posted by bandi

Hi visakh,
>>to kill victim A morderer 01 needed 4 shoots to kill C only 2.
Your inner query returning noOfShoots as 3 only for morderer 01(for A victim) ANd 1 for C

I mean,
You missed that last attempt count there...
So it should be
SUM(CASE WHEN shoot_no <> 'Kill' THEN 1 ELSE 0 END)+1 AS ShootCnt

--
Chandu

in that case no need of +1
just COUNT(*) would do

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-21 : 05:30:09

[code]
SELECT Morderer_Id,SUM(ShootCnt)*1.0/COUNT(*) AS AvgShoots
FROM
(
SELECT Morderer_id,victim_id,COUNT(*) AS ShootCnt
FROM table
GROUP BY Morderer_id,victim_id
HAVING SUM(CASE WHEN shoot_no = 'Kill' THEN 1 ELSE 0 END) >0
)t
GROUP BY Morderee_Id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-21 : 05:34:37

quote:

Originally posted by visakh16


SELECT Morderer_Id,SUM(ShootCnt)*1.0/COUNT(*) AS AvgShoots
FROM
(
SELECT Morderer_id,victim_id,COUNT(*) AS ShootCnt
FROM table
GROUP BY Morderer_id,victim_id
HAVING SUM(CASE WHEN shoot_no = 'Kill' THEN 1 ELSE 0 END) >0
)t
GROUP BY Morderee_Id

Now looks nice

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources