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.
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_NoThird columnt: victim_id01 1 A01 2 A01 3 A01 kill A01 1 B01 2 B01 1 C01 kill C02 1 D 02 2 D02 3 D02 4 D02 kill D02 1 E02 1 F02 1 G02 2 GAND 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 MVPhttp://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 AvgShootsFROM(SELECT Morderer_id,victim_id,SUM(CASE WHEN shoot_no <> 'Kill' THEN 1 ELSE 0 END) AS ShootCntFROM tableGROUP BY Morderer_id,victim_idHAVING SUM(CASE WHEN shoot_no = 'Kill' THEN 1 ELSE 0 END) >0)tGROUP BY Morderee_Id[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 CI 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 CI 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 +1just COUNT(*) would do------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 AvgShootsFROM(SELECT Morderer_id,victim_id,COUNT(*) AS ShootCntFROM tableGROUP BY Morderer_id,victim_idHAVING SUM(CASE WHEN shoot_no = 'Kill' THEN 1 ELSE 0 END) >0)tGROUP BY Morderee_Id[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 AvgShootsFROM(SELECT Morderer_id,victim_id,COUNT(*) AS ShootCntFROM tableGROUP BY Morderer_id,victim_idHAVING SUM(CASE WHEN shoot_no = 'Kill' THEN 1 ELSE 0 END) >0)tGROUP BY Morderee_Id
Now looks nice--Chandu |
|
|
|
|
|
|
|